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.
See this page for how to enable logging of SQL queries generated by Hibernate.
Starting with MySQL 5.1, the query log can be toggled while the MySQL server is running.
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%'; |
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".
The slow query log can provide valuable profiling information.
Turning on the slow query log in MySQL 5.0 must be done while MySQL is not running:
log-slow-queries=\path\to\slow_queries.log |
log-slow-queries=/path/to/slow_queries.log |
mysqldumpslow
is a useful tool to aid interpretation of the slow query log.