Optimizing Queries

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:

• adding indexes

Query Logging

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

1. Open MySQL Administrator.
2. In the left pane, click Startup Variables.
3. Select the Log files tab.
4. Select the items you wish to log from the Activate Logging Section. To see the queries, check the box next to "Query Logfile Name."
5. Click Apply Changes.
6. In the left pane, click Service Control.
7. Stop and restart the service. This step allows the service to restart with the logging parameters.
8. 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".

MySQL Slow Query Log

The slow query log can provide valuable profiling information.

Setup

Turning on the slow query log in MySQL 5.0 must be done while MySQL is not running:

1. shutdown mysql
2. (Windows) add the following lines to my.ini:

   log-slow-queries=\path\to\slow_queries.log
   

3. (non-Windows) add the following lines to my.ini:

   log-slow-queries=/path/to/slow_queries.log
   

Analysis

mysqldumpslow is a useful tool to aid interpretation of the slow query log.