How to determine cause of slow queries

Question

I have a query that is taking around 7 seconds. I break it into two queries which provides the same data as the first, and it takes around 0.01 seconds. I had thought that I properly indexed it, however, likely had not. The database currently has very little data in it. I am using MySQL 5.5.46. I am using PHP with PDO, however, I don't think that is relevant, and purposely did not tag this question with PHP or PDO.

I am not asking why my specific query is taking so long or how to identify slow queries, but am asking for the generic steps to determine the cause of a slow query. I expect that EXPLAIN will be used. What are you looking for in EXPLAIN? What other steps could one take?

Answer 1

Spencer7593's is a very good place to start, but you aren't going to get a full answer there, or here on StackOverflow. A partial explanation took me about 40 pages full.

EXPLAIN is useful - but needs to be read with an understanding of the structure of the tables and indexes - from your description, it seems likely that the optimizer is ignoring an index. You can force the DB to use a particular index for a query, but its a rather untidy solution (even if you know that's the best solution today, it might not be in future).

If you have a perfectly good index and the DBMS is not using it, then the most likely cause is that the cardinality stats have not been updated - but it can also occur when the data is very skewed (e.g. if you have 10000 values of 'A' and 2 of 'B' then an index will help you find records with 'B' but not records with 'A').

Always using an index does not always make your queries faster - sequential reads from a single file are much faster than random reads on 2 files.

Another caveat is that MySQL does not handle push predicates very well.

Beware of implicit (and explicit) type conversions in Joins - MySQL can't use indexes for these. Mariadb supports virtual columns (which can be indexed). Hence if you

...
tab_a INNER JOIN tab_b
ON UNIX_TIMESTAMP(tab_a.datetime)=tab_b.seconds_since_epoch

the optimizer can use a index on tab_b.seconds_since_epoch, but not one on tab_a.datetime.

With some engines (and with named locks) queries can be blocked by other activity in the DBMS - although such cases usually manifest from stats based analysis of DBMS performance, and is unlikely to be the cause here. There's another step required to track down what's doing the blocking.

Decomposing the query into smaller parts and testing them independently is an excellent diagnostic tool (kudos!) but its only when you look at all the EXPLAIN plans that you can understand why you get aberrant behaviour in the composite.