We store data with the intention to use it: search, retrieve, group, sort... To perform these actions effectively MySQL storage engines index data and communicate statistics with the Optimizer when it compiles a query execution plan. This approach works perfectly well unless your data distribution is not even.
Last year I worked on several tickets where data follow the same pattern: millions of popular products fit into a couple of categories and rest used the rest. We had a hard time to find a solution for retrieving goods fast. Workarounds for version 5.7 were offered. However new MySQL 8.0 feature: histograms, - would work better, cleaner and faster. This is how the idea of the talk was born.
I will discuss
- how index statistics physically stored
- which data exchanged with the Optimizer
- why it is not enough to make correct index choice
In the end, I will explain which issues resolve histograms and why using index statistics is insufficient for fast retrieving of not evenly distributed data.
Sveta Smirnova works as MySQL Support engineer since year 2006. She is author of book "MySQL Troubleshooting" (http://shop.oreilly.com/product/0636920021964.do) and MySQL Labs project "JSON UDFs for MySQL": design prototype of today JSON support in MySQL. After starting her career as a web developer, Sveta moved to a role in Bugs Analysis MySQL Support Group in MySQL AB/Sun/Oracle, then, in March 2015, joined the Support Team in Percona. She works on MySQL software bugs, escalations and tricky Support issues on a daily basis. Her main professional interest in recent years is finding better ways to effectively solve DBA problems.