Unofficial MySQL 8.0 Optimizer Guide

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.

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.

Are Window functions definitely coming in 8.0?

Author here (and product manager for MySQL).

We do not have a date yet for MySQL 8.0, but it is coming closer. We are on a ~2-3 year release cycle with 5.7 released Oct 2015.

Many of the features of this guide are now specific to MySQL. MariaDB diverged from MySQL 5.5 (2010).

We're only considering upgrading to 5.7 in production now, so I'd guess that 5.8/8.0 is still 2-3 years from being production-ready.

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).

So you can see the invisible indexes page does this (8.0 only): http://www.unofficialmysqlguide.com/invisible-indexes.html

But there are subtitles where I don't go into full detail. For example here it didn't seem right to say that descending indexes are 8.0 only:

http://www.unofficialmysqlguide.com/composite-indexes.html#d...

(In my next update I'll have a section on descending indexes and I'll be specific here.)

The format of OPTIMIZER_TRACE looks different around things like sorting data. I'll always use the newer diagnostics which are more verbose.

Author here - some of it applies to all databases generically (performance characteristics of a B+tree and cost-based optimization).

But MariaDB diverged from MySQL 5.5 (2010); so there is a lot which is different.