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.
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.
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.
I'll be expanding it in the coming months as the new features in MySQL 8.0 are released. On my TODO is descending indexes, improvements to OPTIMIZER TRACE, Window functions, CTEs and expanding the info on character sets.
It has not passed New Feature Qualification[1] yet, which has to be done before it can be merged to trunk. You can see the state of it from a presentation earlier this month:
Does anyone have a good comparison between MariaDB 10.x and MySQL 8.0? Also, when will MySQL 8.0 be out? (The current version is "Not yet released, Development Milestone").
My goal is to explicitly state something as "new" when it is first in 8.0; but not mention the version when referring to older (mostly because it makes readability harder).
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-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.