3 ms·

I was conflicted when I read "3. Most selective columns to the left": http://www.unofficialmysqlguide.com/composite-indexes.html#determining-the-order-for-comp

I was conflicted when I read "3. Most selective columns to the left": http://www.unofficialmysqlguide.com/composite-indexes.html#determining-the-order-for-composite-indexes http://www.unofficialmysqlguide.com/composite-indexes.html#d... Because I had previously read that it was a myth: https://use-the-index-luke.com/sql/myth-directory/most-selective-first https://use-the-index-luke.com/sql/myth-directory/most-selec... Both this guide and Use The Index Luke seem to be good resources. Later I realized that maybe the guide had listed this optimization tips in order of importance. After all "1. Leftmost rule" and "2. Ranges to the right" are the ones that affect the most the usability of the index. Then the seemingly opposite viewpoints converge.

barrkel 10y ago
The example given for selective columns on the left is bogus, though; of course a composite index can't be used if the first column in the index isn't used by the predicate - and this has nothing to do with the selectivity of the column. Selectivity of the column in principle matters to reduce the time spent doing comparisons to find the right slot in each btree page; if more selective columns are at the end, the comparison function will have to scan through more of the index data to determine ordering. Much more important, IMO, is reuse of the index. If you frequently use predicates referencing N-1 columns and have an index on N columns, you're better off if the least used column is in the Nth position. I think this is what the article was trying to get at, if somewhat hamfistedly.
- morgo 10y ago
  Author here - Yes, I agree with you on re-use of the index. This is sort of left-most rule, but I should really break it out into its own point.
morgo 10y ago
Author here. Yes - the text right above this says that: "Determining the correct order of columns in a composite index is tricky. Here are some considerations to be aware of .." I think myth is a strong word. It holds truth, but there are just so many exceptions.

barrkel 10y ago

The example given for selective columns on the left is bogus, though; of course a composite index can't be used if the first column in the index isn't used by the predicate - and this has nothing to do with the selectivity of the column. Selectivity of the column in principle matters to reduce the time spent doing comparisons to find the right slot in each btree page; if more selective columns are at the end, the comparison function will have to scan through more of the index data to determine ordering. Much more important, IMO, is reuse of the index. If you frequently use predicates referencing N-1 columns and have an index on N columns, you're better off if the least used column is in the Nth position. I think this is what the article was trying to get at, if somewhat hamfistedly.

morgo 10y ago
Author here - Yes, I agree with you on re-use of the index. This is sort of left-most rule, but I should really break it out into its own point.

morgo 10y ago

Author here. Yes - the text right above this says that: "Determining the correct order of columns in a composite index is tricky. Here are some considerations to be aware of .." I think myth is a strong word. It holds truth, but there are just so many exceptions.