Here’s an excerpt of this article:
SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990;
MySQL is first scanning an index then retrieving rows in the table by
primary key id. So it’s doing double lookups and so forth.
The following piece just uses the primary key:
SELECT id FROM customers ORDER BY name LIMIT 10 OFFSET 990;
I don’t figure out the difference between those two queries despite the explanation, especially the evoked double lookup…
May someone explain it in more detail?
The table has a secondary index on
name(let’s call it idx_name for the reference). Internally MySQL stores records in secondary indexes as a pair of (key, value), where key is indexed field and value is the primary key. In this case it will be (name, id).
To execute the first query MySQL decided to use index
idx_name. But in order to satisfy the query it has to take
id for each value of
name and go to PRIMARY index in order to get
phone values. That’s why they call it “additional lookup”.
For the second query MySQL has all necessary fields in index
idx_name. Remember, id is the part of the index?