
Unofficial MySQL 8.0 Optimizer Guide - digitalnalogika
http://www.unofficialmysqlguide.com/
======
javitury
I was conflicted when I read "3\. Most selective columns to the left":

[http://www.unofficialmysqlguide.com/composite-
indexes.html#d...](http://www.unofficialmysqlguide.com/composite-
indexes.html#determining-the-order-for-composite-indexes)

Because I had previously read that it was a myth:

[https://use-the-index-luke.com/sql/myth-directory/most-selec...](https://use-
the-index-luke.com/sql/myth-directory/most-selective-first)

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
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
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
Author here. Thanks for linking to my guide :)

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.

Suggestions/comments welcome!

~~~
derekperkins
Are Window functions definitely coming in 8.0?

~~~
morgo
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:

[https://www.slideshare.net/DagHWanvik/sql-window-
functions-f...](https://www.slideshare.net/DagHWanvik/sql-window-functions-
for-mysql)

[1] [http://mysqlserverteam.com/new-feature-
qualification/](http://mysqlserverteam.com/new-feature-qualification/)

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

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

------
GrumpyNl
That's some great info, thanks for the effort.

------
alashley
Thank you for posting this, I've been wanting to further my knowledge of
databases and database design.

------
ysleepy
Yeah nice try Oracle, MySQL will not gain its community traction back.

------
caleblloyd
Great write-up! Which of these features are new in 8.0 and not in 5.7?

~~~
morgo
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](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...](http://www.unofficialmysqlguide.com/composite-
indexes.html#determining-the-order-for-composite-indexes)

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

------
compuguy
I'm guessing this may also apply to MariaDB (MySQL fork) as well?

~~~
morgo
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.

------
snissn
I expected to see a postgresql migration guide

