Can't figure out this "trick" to optimize MySQL query

Question

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?

Answer 1

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 address and 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?