2

I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries:

  1. number of joins that use ranges on the first table
  2. number of joins that do a full scan of the first table
  3. number of joins without keys that check for key usage after each row
  4. number of joins that perform table scans because they do not use indexes
  5. number of joins that use a range search on a reference table

Of these queries, which ones are the least performing and hence should be optimized first? I'd say #2 and #4 in order of importance, but I'd like to hear comments about the other types too.

This monitoring system shows also the total number of SELECT queries as the sum of these five values. Are these really all possible, mutually-exclusive cases for a SELECT?

I'm not talking about a specific query (which I can find in the slow query log); I'm more interested in the type of query amongst these 5 listed above. For instance, full table scans are usually a bad thing (unless we're working on small tables, in which case we won't care about optimization).

My other question was if these types may be overlapping, because the monitoring system shows a 6th value which is the sum of these five, and this makes no sense if the types are not mutually exclusive (as we suspect).

4

None of the above.

A "full scan" is irrelevant if the table has only 10 rows. Ditto for "not using an index".

There are many queries for which no index is useful; you stats will have you stumbling over them. For example, if you index a 'flag' (yes/no, M/F, etc), the optimizer will usually eschew the index and simply scan the table -- because it is more efficient to do so.

"First table". Unless you are looking at the EXPLAIN, it is difficult to predict which table will be looked at "first". Even the existence of LEFT does not conclusively predict ordering of the tables.

What is your goal, anyway? The main goal, in my opinion, is to find and fix queries that are bogging down the system. To that end, I want a list of queries, ordered by total elapsed time (number of occurrences times avg time). An that is very efficiently provided by

long_query_time = 1
turn on slowlog
wait a day
use one of these to get the list:
    mysqldumpslow -s t
    pt-query-digest

Then, I get EXPLAIN and SHOW CREATE TABLE for the first couple of queries and work on them.

If you want to see 'change over time', then record the 'total time' for the worst queries and see it those get better or worse next month.

You may find that some of the worst queries are not SELECTs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.