There are a few approaches you may take to find slow queries. The best one with which to start is to enable the slow query log by adding slow_query_log server option to your server start-up or options files. You also need to set the long_query_time to filter which queries are slow. This will log slow queries together with the time it took to execute them to [hostname]-slow.log in your MySQL server data directory. If you have a large number of slow queries, you might start with higher values which will show the slowest queries and gradually reduce your settings until you’re satisfied with the results.
You could also enable the log_queries_not_using_indexes option, which will log queries that are performing full table scans. Some of such queries are normal. However, you might wish to review these as they are frequently the reason for performance problems. You may also use mysqldumpslow utility to parse your slow query log and to summarize all similar queries. This utility is especially helpful if you have many slow queries.
Besides the slow query log, you might want to look at the processlist using the “SHOW PROCESSLIST SQL” statement. Sometimes you can see a query which is so sub-optimal, one which never finishes. Such queries that never finish will never register in the slow query log and will sometimes run for weeks until the server is restarted. You also should also check the processlist and look for any queries appearing frequently. These might be fast enough not to register in the slow query log, but also frequent enough to drain server resources.
Once you’ve discovered candidates for optimization, use the “EXPLAIN SQL” statement to see if particular queries are executing optimally.