I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries:
- number of joins that use ranges on the first table
- number of joins that do a full scan of the first table
- number of joins without keys that check for key usage after each row
- number of joins that perform table scans because they do not use indexes
- 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).