5 ms·

Unofficial MySQL 8.0 Optimizer Guide

186 points by digitalnalogika 10y ago

GrumpyNl 10y ago
That's some great info, thanks for the effort.
alashley 10y ago
Thank you for posting this, I've been wanting to further my knowledge of databases and database design.
wolf550e 10y ago
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").
- VintageCool 10y ago
  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.
- morgo 10y ago
  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).
compuguy 10y ago
I'm guessing this may also apply to MariaDB (MySQL fork) as well?
- morgo 10y ago
  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.
javitury 10y ago
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.
morgo 10y ago
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 10y ago
  Are Window functions definitely coming in 8.0?
  - morgo 10y ago
    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-for-mysql https://www.slideshare.net/DagHWanvik/sql-window-functions-f... [1] http://mysqlserverteam.com/new-feature-qualification/ http://mysqlserverteam.com/new-feature-qualification/
snissn 10y ago
I expected to see a postgresql migration guide
caleblloyd 10y ago
Great write-up! Which of these features are new in 8.0 and not in 5.7?
- morgo 10y ago
  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#determining-the-order-for-composite-indexes 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.
ysleepy 10y ago
Yeah nice try Oracle, MySQL will not gain its community traction back.

GrumpyNl 10y ago

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

alashley 10y ago

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

wolf550e 10y ago

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

VintageCool 10y ago
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.
morgo 10y ago
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).

compuguy 10y ago

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

morgo 10y ago
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.

javitury 10y ago

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.

morgo 10y ago

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 10y ago
Are Window functions definitely coming in 8.0?
- morgo 10y ago
  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-for-mysql https://www.slideshare.net/DagHWanvik/sql-window-functions-f... [1] http://mysqlserverteam.com/new-feature-qualification/ http://mysqlserverteam.com/new-feature-qualification/

snissn 10y ago

I expected to see a postgresql migration guide

caleblloyd 10y ago

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

morgo 10y ago
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#determining-the-order-for-composite-indexes 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.

ysleepy 10y ago

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