The EXPLAIN statement causes MySQL to reveal how a particular query is executed. This information is obviously quite helpful in obtaining information necessary to optimize slow or costly queries.
The MySQL Query Browser GUI tool allows query explanation under the Query->Explain Query menu item.
Common steps taken to optimize queries include:
Query logging can be useful for finding out which specific actions in the UI cause which queries to be executed. These queries can subsequently be EXPLAINed, as mentioned above.
IMPORTANT NOTE: Full query logging impacts performance, so you should NOT apply all query logging to production servers.
Hibernate query logging
See this page for how to enable logging of SQL queries generated by Hibernate.
MySQL Query Logging
Starting with MySQL 5.1, the query log can be toggled while the MySQL server is running.
Via the MySQL Administrator GUI
Open MySQL Administrator.
In the left pane, click Startup Variables.
Select the Log files tab.
Select the items you wish to log from the Activate Logging Section. To see the queries, check the box next to "Query Logfile Name."
Click Apply Changes.
In the left pane, click Service Control.
Stop and restart the service. This step allows the service to restart with the logging parameters.
To check the logs you can:
Click Server Logs in the left pane and select the General Query Log tab. You should see a number of SQL statements. You can see additional statements by clicking Refresh.
Look in the 'data' directory of your MySQL installation. You will see logs corresponding to the items you selected in the previous steps.
Via the MySQL console
mysql> SET GLOBAL general_log_file = '/tmp/query.log';
mysql> SET GLOBAL general_log = 'ON';
To inspect the current setting
mysql> SHOW VARIABLES LIKE '%general_log%';
Via Mifos Hibernate configuration
The "SCHEMA.database.hibernate.show_sql" property can also be set to "true" in LocalPropertiesFile to enable Hibernate query logging. SCHEMA can be, for instance, "acceptance", "integration", or "main".