how to determine slow queries?

Question

My site is experiencing a really slow loading time. I have suspected that it might be javascript or php that causes longer loading time but i have tested my site in YSlow and its grade is B which i think is not bad.

Now i want to check my database if something is wrong with queries, database indexing that causes my site to load slower.

Is there some tutorials or tricks i might read or try to test database to figure out if there is slow queries? Any tips for database management?

Answer 1

I always fall back to 2 mantras for faster query execution -

1. Indexes, indexes and indexes.
2. Try to get rid of JOINS as much as possible.

There are some tried and tested methods to weed out slow queries. You need to turn on slow query log. This logs all those queries which take more than x seconds to execute. x is specified by you in mysql.conf.

Once the slow queries start logging in the log. You can analyse each query using EXPLAIN and appropriately add indexes to speedy the query execution.