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.

Answer 2

For most of all, if you have a possibility to use phpmyadmin, there is a great profiler.

After you invoke your query in phpmyadmin, you have option to use "profiling" (right before edit anchors)

It gives you a nice graph and table with jobs and timings, so I think it would be helpfull.

Answer 3

Well this is very generic but I will try to provide some guidelines

• Number one is Index if you perform a search over a field you need an index for that field.
• Now if you perform index over multiple fields instead of multiple index you probably need a composite index instead.
  • Filter a subquery doesnt use index so be carefull if you try to filter over a subquery.
  • Also using function on the WHERE doesnt use index, things like SUBSTRING , UPPER CASE or LIKE
• Using INNER JOIN without ON will cause a CROSS JOIN and multiply the number of rows very fast.

In the Query Execution Plan you try to look for FULL SEQ SCAN instead of INDEX SCAN

Answer 4

Explain shows subqueries, which indexes are actually used, how many rows it has to scan etc. See mysql manual on its output

Then there goes a magical "staring on it" method, which usually produces ideas on how query complexity can be reduced:

• less queries is better
• indexes are better than full table scan
• joins can be better than subqueries
• less joins are better (because joins increase scanned rows, sometimes in multiple times)
• more selective indexes are better than less-selective, so that after indexing less rows remain to scan
• grouping and sorting are additional cost
• having can be more expensive than where (because works after grouping)

and so on

Answer 5

Steps to optimise the run-time of a query. You should check the speed of the query after each step - if you could make any changes to it in a particular step.:

1. Take a look at your query in general and try to confirm that it only queries what it is supposed to query. Look for unused fields, unnecessary joins, unncessary outer joins. Consider using limit to limit the number of records returned. Remember, assembling a larger resultset than needed also requires additional time to create and send over to the client.
2. Now, take a closer look at your query again and see if you can simplify it. For example you may have subqueries in the select list, which you can try to convert into derived tables. Also take a look at your where criteria and confirm if they can use indexes (expressions, like '%xxx%'. If they cannot, then check if you can change them into sg that can use an index.
3. If you have any indexes on any affected tables, it is time to refresh them using analyse table command just to be on the safe side of things. Check the cardinality of the existing indexes. Mysql is less likely to use indexes with low cardinality. If the cardinality is far from what you think it should be (number of unique values in a given field), then you may want to tweak how mysql samples the data to calculate cardinality.
4. Run explain and check if
   • indexes are used where you expect them (possible keys, used keys)
   • avoid join type ALL and file sort

Try adding indexes to those parts of the query where they are not used or if you belive you already have indexes in place, then use index hints, such as force index to make mysql use your index.

5. If the query is still slow, then you may have to tweak server side variables, use different table engine, partition a table, change data structure (denormalisation), archive old data to reduce size, etc.

You could write long articles for each individual step, or in case of no. 5, about each item.