WL#1021: Add exception Value list for index
Affects: Server-7.1 — Status: Un-Assigned
One of problems of current optimizer is joining the tables where lookup one has very different data distribution. Consider: select * from a,b where a.c1=const and b.c=a.c2; In this case optimizer can't use constant based estimation for table "b" but only to use cardinality value. If table b will have very uneven value distribution, for example 1010 rows, 11 distinct values. 1 met in 1000 rows and others each in one. The MySQL optimizer in such case will do index in lookup tables for all values, which is not good. The same can happen other way around - full table scan can be selected for value which really has just a few rows matching. The fix: MySQL shall be able to detect what for this case the variance of distribution is large and so it shall select table scan or range scan for each outer loop constant. There are two solutions how to select it - real data based. Where we do the range check for each value to decide if we're to use index or full table scan. Is generally slow. Usable for some cases however (ie small outer table) More general approach is to use some sort of extended statistics, which covers these "exceptional" values. It can be done as simple excpetion list + cardinality as well as histogram. The histogram is usually build for some fixed, size a way to have same amount of rows for each histogram part. We shall also store amount of NULL values to be able to handle NULL searches as well. The histogram building example: (There are variations possible) NULL NULL 1 1 2 2 2 2 2 3 4 5 5 5 Total 12 values + 2 NULLs Let us build histogram with 4 parts. Each part matched to 4 rows: 1 1 2 ------- 2 2 2 ------- 2 3 4 ------- 5 5 5 Which can be stored as: NULL=2 TOTAL=14 1-2|2-2|2-3|5-5 For large row number we can assume number hi(n)=low(n+1) and store just min=1|2|2|3|5 which means: 1-2|2-2|2-3|3-5| Having such histogram we can check How many rows matches 2? 2 is met in 3/4 ranges -> 9 rows how many rows matches 1? 1 is met in 1/4 ranger -> 3 rows How many rows have value 8? It is not in the histogram -> 0 rows (note we shall be careful as MySQL in some cases assumes 0 is accurate) how many rows are in range 1-4 4/4 -> 12 rows We possible can have histogram size adjustable on index level in long run. Histograms allows you to cover "exceptions" as well. As having histogram with 10 values would have dedicated positions for exceptions having 10+% values, basically having the same info as exception list.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.