Find out what mysql queries are slow

This entry was posted by on Wednesday, 2 June, 2010 at

So you are using mysql and have some data in your DB … say 2 million rows or something.  Not it’s about time to see how your SQL queries are doing, do some profiling, but … but how to you find out what sql queries are slow? Where you are missing that index? Try the following in your mysql.ini

[mysqld]
#enable slow query logs
log-slow-queries = d:/your/path/to/log/slow.log
# default of “slow” is 10 seconds, set it to 1
long_query_time = 1
# log *EACH AND ANY* query not using an index (this may be a lot till you fix it)
log-queries-not-using-indexes


Then you see where you’re not using the index, where you forgot it. Maybe use EXPLAIN on some of your longer queries to find out how to improve them

[mysqld] 
#enable slow query logs
log-slow-queries = d:/phpapps/xampp/mysql/log/slow.log
# default of "slow" is 10 seconds, set it to 1
long_query_time = 1
# log *EACH AND ANY* query not using an index (this may be a lot till you fix it)
log-queries-not-using-indexes

Leave a Reply