How to determine what should be the order of the fields in a composite index? For example, if the table has two fields:

`refund_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `status` smallint(6) DEFAULT '0',

while the status field is with low selectivity (it can only take values ​​of 0, 1 or 2), which of these fields should be first in the index, and which one - second?

    1 answer 1

    Does not matter. If the selection goes on two fields, then the whole index is used as a single entity.

    But when selecting fields from an index, the index will be used only if these fields are located at the beginning of the index

    Update

    As noted by @ Fat-Zer, the index will not be used in the case of sorting by index fields, if the fields for sorting are specified in a different order than in the index

    • In fact, it is absolutely not “all the same” ... it depends on the requests and if the order is incorrectly chosen, the index will simply not be used. - Fat-Zer
    • @ Fat-Zer Give an example of a query that uses all the fields of the index, while the index itself is not used? - Anton Shchyrov
    • one
      elementary, for example, any query with ORDER BY in the reverse order, that is, if there is an INDEX (refund_date, status) , then SELECT … ORDER BY status, refund_date will cause a complete sorting of all records. More vital options include grouping on the second field with a filter on the first ... - Fat-Zer
    • @ Fat-Zer Thank you for sorting, I have answered the answer. And for filtering, I clearly wrote If the selection goes on two fields . Of course, if you filter only by the second field, then the index goes through the forest. Well, and this is written in the answer - Anton Shchyrov
    • 2
      “All the same” in what order the index is only in the most primitive case, when the WHERE contains both fields in an independent form ... and the reverse ORDER BY is just one counterexample, when it will be broken ... creating correct indexes and query optimization is a fairly extensive topic, and the correct answer to the question will not be “all the same”, but “it all depends on the queries that will use this index”. - Fat-Zer