Once you're sure your queries retrieve only the data you need, you can look for queries that examine too much data while generating results.
Rows examined and rows returned
It's useful to think about the number of rows examined when analyzing queries, because you can see how efficiently the queries are finding the data you need. However, this is not a perfect metric for finding "bad" queries. Not all row accesses are equal. Shorter rows are faster to access, and fetching rows from memory is much faster than reading them from disk. Ideally, the number of rows examined would be the same as the number returned, but in practice this is rarely possible. For example, when constructing rows with joins, the server must access multiple rows to generate each row in the result set.
Rows examined and access types
When you're thinking about the cost of a query, consider the cost of finding a single row in a table. MySQL can use several access methods to find and return a row. Some require examining many rows, but others might be able to generate the result without examining any.
The access types appear in the type column in EXPLAIN's output. The access types can be looked up on MySQL Explain. Each of these is faster than the one after it, because it requires reading less data. You don't need to memorize the access types, but you should understand the general concepts of scanning a table, scanning an index, range accesses, and single value accesses. If you aren't getting a good access type, the best way to solve the problem is usually by adding an appropriate index. Indexes let MySQL find rows with a more efficient access type that examines less data.
MySQL apply a WHERE clause
In general, MySQL can apply a WHERE clause in three ways, from best to worst:
- Apply the conditions to the index lookup operation to eliminate nonmatching rows. This happens at the storage engine layer.
- Use a covering index to avoid row accesses, and filter out nonmatching rows after retrieving each result from the index. This happens at the server layer, but it doesn't require reading rows from the table.
- Retrieve rows from the table, then filter nonmatching rows. This happens at the server layer and requires the server to read rows from the table before it can filter them.