0

For example, if a table has columns A, B, ID, and (ID) is the primary key, and (A) is a secondary index.

I wonder if the leaf nodes on the secondary index (A) is sorted by (A,ID), or just (A), and the order of ID is not required?

The question comes from the reading of High Performance MySQL,3rd Edition, in which the author says

... Recall that in InnoDB, an index on column (A) in our example table is really equivalent to an index on (A, ID) because the primary key is appended to secondary index leaf nodes. If you have a query such as WHERE A = 5 ORDER BY ID, the index will be very helpful...

I don't understand why by using (A) we can speed up the query of a ORDER BY ID. This can be true only when in the secondary index (A), leaf nodes are not only sorted by A but also sorted by ID.

Improve this question
New contributor
Name Null is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

2 Answers 2

Reset to default
1

MySQL does not have the concept of "INCLUDED" columns for indexes, so appending the PK column(s) to the secondary index means literally extending the index with more "indexed" columns. The indexes (A) and (A, ID) are really the same here - InnoDB sees "CREATE INDEX table(col)" and internally translates that to "CREATE INDEX table(col, id)".

InnoDB does the INCLUDED columns with the table itself already, technically the table is an index on PK with all the remaining columns included as non-indexed. So it could possibly support it for the PK case too. But having the secondary indexes leaf nodes be ordered by ID gives the benefit of being able to access the table (the PK index) in the right order without additional sorting.

Improve this answer
0

I don't understand why by using (A) we can speed up the query of a ORDER BY ID.

In a query with WHERE A = 5 ORDER BY ID, the matching rows are all tied with respect to the A column. If they are fetched by ID order, then the result has no additional work to do to ensure it is sorted by ID. The query simply returns the result in the order they are read from the index.

Improve this answer
3
  • I'm sorry I didn't clarify the question. I know an ordered ID in the leaf nodes of index (A) can eliminate sorting. What I really wanted to ask is whether ID is ordered. Commented 22 hours ago
  • ID is ordered. You can see this when using certain EXPLAIN variations. They won't have a "sort" pass. Commented 22 hours ago
  • Yes, an ID is ordered. That has to be true, or else the optimizer can't eliminate the extra sort. Commented 21 hours ago

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.