2

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?

2

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?

  • Great explanation. I was ignorant about this way of doing from Mysql :) Thanks a lot – Mik378 Jun 8 '14 at 14:15

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.