How can i log which all query is executed,how many secs a MYSQL query take to execute, to increase performance of database? I am using PHP
+1
A:
Use the slow query log for catching queries which run longer than a specified time limit (2 seconds by default). This will show you the worst offenders; probably many will be low-hanging fruit, fixable by proper indexes.
It won't catch a different type of code smell: a relatively fast query running many times in a tight loop.
If you wish, you may set the slow query limit to 0 and it will log all queries - note that this in itself will slow down the server.
Piskvor
2010-09-16 09:22:18
I ran the above command in a console and executed my app frm browser, but nothing is logged :( What to do?
Ashwin
2010-09-16 12:11:43
@Ashwin: A better option is to change the settings in your MySQL configuration file. If no slow queries are logged, it means you don't have any queries running longer than the specified time - do you actually *have* a measurable performance problem? As I said, you could set the slow query limit to 0 in your config, but that amount of logging may slow down the server.
Piskvor
2010-09-16 12:17:10
yeah did that nothing is logged, but when i gave log-queries-not-using-indexes
Ashwin
2010-09-16 13:03:52
@Ashwin: You don't seem to have any slow queries, then :) Congratulations.
Piskvor
2010-09-16 13:19:19