To find slow queries in MySQL, you need to turn on logging in your configuration file. You can set up the logging so that every time a query takes longer than a certain amount of time, the query gets logged in a file. You can then examine the file to find your bottlenecks and fix them. Here are the steps to turn on the logging.

You need to edit your my.cnf file which is typically found in the /etc directory. Something like so should do it: vi /etc/my.cnf. Depending on how your server is set up, you may need root access to perform this edit.

Once opened, edit the my.cnf file by adding the following lines in the [msysqld] section:

log-slow-queries = /var/log/slow-query.log
long_query_time = 2

In this example, the slow queries will be logged into a file called /var/log/slow-query.log, you can set this to anything you like. In addition, this example logs any query that takes longer than 2 seconds. You can adjust this value to whatever you like.

For this to work, you need to create the file into which the logging will occur (/var/log/slow-query.log in this case) and give it update the owner of the file to be the mysql user (using the chown mysql /var/log/slow-query.log command once the file is created).

Once the log file is in place, restart your mysql server (service mysql restart) and the logging will begin.

Once you find your slow queries in the log and fix them, it's a good idea to once again update the my.cnf file to remove or comment out the lines that cause the logging to happen. The logging does increase the load on the database, so once things are in order you don't want that additional overhead. You can turn it on occasionally to make sure that your performance is still good.