If a table contains several indexes and a select query does not have a “force index” hint clause, the MySQL optimizer take charge of determining which index to use. In some cases, we find that MySQL optimizer does not select the best index.
Here we discuss a strategy to deal with the problem, using a new parameter.
1、table and index statistics
The statistics contains the number of different key values in a given index, which affects the decision of index selection.
In a big table, it is not workable that scans all the rows and counts the accurate statistics. So InnoDB pick up some example rows to estimate the result. The variable “innodb_stats_sample_pages “is used to define the number of example rows. Its default value is 8.
There should be a balance consideration about this variable. Because the function “dict_update_statistics”, which is used to update a table’s statistics, is automatically called at various times. The bigger the innodb_stats_sample_pages is, the more accurate result comes, but it may lead to excessive I/O and CPU.
2、When the dict_update_statistics called?
There are some scenarios that dict_update_statistics will be called.
a) Statements like “show status”
Such as “show index from table-name” and “show status like ‘’”. There is a variable named “innodb_stats_on_metadata” to control whether run dict_update_statistics in such queries. Default value is ON.
b) Table monitor
When a table named “innodb_table_monitor”(InnoDB table) is created, table monitor will be run every minute. “dict_update_statistics” is called here.
c) Analyze table table-name
d) Dynamically during insert/update operation.
This is why the innodb_stats_sample_pages cannot be set too big. There is a counter to record the times of modifying indexed column of this table from last dict_update_statistics. When the counter is up to 2000000000 or 1/16 of the table row number, dict_update_statistics is called.
3、Force re-calculate statistics using analyze table
Let’s look into the next steps:
Set innodb_stats_sample_pages = BIGNUM;
Analyze table table-name;
Set innodb_stats_sample_pages = 8;
Obviously this does not make sense, for the reason of d) in last section.
4、Strategy for certain requirement
But let’s think about this case, when there are lots of rows in a table, and the row number will keep relatively stable in a period.
We run the analyze table command, and test the queries that will run upon this table, check that the index selection working well.
Since the dynamically re-calculating may get wrong statistics and then leads to wrong index-selection, we plan to disable the mechanism, using a variable that can be update by command “set global”.
The variable can be named “innodb_stats_dynamically”, ON as default.
So when we think the data number is big enough and will be relatively stable in a period afterward, the sample commands can be as follow:
Set innodb_stats_dynamically = off;
Set innodb_stats_sample_pages = BIGNUM;
Analyze table table-name;
Set innodb_stats_sample_pages = 8; (optional)
