mysql index

In the MySQL secondary index, are the primary keys on the leaf nodes ordered?

Physical structure in MySQL secondary index

  • is the primary key ordered? No, of course.
    – Akina
    Dec 15, 2022 at 16:24
  • When my query condition hits the secondary index, why is the returned data sorted by the primary key?(One explanation is that the primary keys in the leaf nodes are ordered) Dec 15, 2022 at 16:36
  • why is the returned data sorted by the primary key After index seeking/scanning the server accesses the table body for the values of the columns which are not present in your secondary index. By some reason the server decides that the rows sorting with further sequental table body reading will be more fast than random rows retrieving. Make your secondary index covering (add columns to the end of the expression, or remove some output columns) - the rows order must change. Anycase - without ORDER BY with unique ordering expression the rows order is not defined, and it may alter at any time.
    – Akina
    Dec 15, 2022 at 17:18

1 Answer 1

  • If you want the results in a particular order, you must include an ORDER BY clause.

  • Results being returned in PK order sounds like the index was ignored. There are many reasons why the Optimizer may eschew the obviously good index. (See other Questions that ask 'why is my index not being used'.)

  • Each secondary index implicitly includes the column(s) of the PK. Then, if the WHERE clause filters down to specific values for the secondary column(s), it is logical for the results to be in PK order.

  • If you want to discuss more, please provide SHOW CREATE TABLE, the SELECT, and EXPLAIN SELECT ...

  • Each secondary index implicitly includes the column(s) of the PK.I have 3 questions. 1.Is index (foo) actually index (foo,pk) ? 2.Will different pk values ​​be in different leaf nodes? 3.Will the id value be saved repeatedly in the leaf node data area? Dec 16, 2022 at 6:34
  • @feilonggao - I addressed your questions in new bullet items. However, I don't think (foo,id) exhibits any repetition of "id".
    – Rick James
    Dec 16, 2022 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.