1

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

Improve this question
4
  • 1
    Maybe you want to check which query is the slowest try the mysql slow query log to enable this use mysqld --log-slow-queries[=file_name] It will log on a file the slow queries Commented Sep 16, 2010 at 9:12
  • 1
    Create a wrapper function to handle all queries, that way you can time each on and save it to a log. Commented Sep 16, 2010 at 9:13
  • Let me do a search for you: stackoverflow.com/search?tab=votes&q=[mysql]%20performance Commented Sep 16, 2010 at 9:29
  • I ran the above command in a console and executed my app frm browser, but nothing is logged :( What to do? Commented Sep 16, 2010 at 11:49

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I ran the above command in a console and executed my app frm browser, but nothing is logged :( What to do?
@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.
yeah did that nothing is logged, but when i gave log-queries-not-using-indexes
@Ashwin: You don't seem to have any slow queries, then :) Congratulations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.