MySQL Optimizer

ANALYZE

See: OPTIMIZE TABLE

Performing a manual ANALYZE on a TABLE is generally not required.
By default TABLE Statistics in MySQL are automatically refreshed when approx 10% of Rows have changed.
If you have busy tables and the optimiser doesn't appear to be making good choices you could consider using ANALYZE.
If your table contains skewed data and might benefit from hsitograms you should consider using ANALYZE.
NOTE: Running ANALYZE when the database is busy is likely to lead to issues.

ANALYZE TABLE mytable;

ANALYZE TABLE mytable UPDATE HISTOGRAM ON mycol1;

ANALYZE TABLE mytable UPDATE HISTOGRAM ON mycol1 WITH 9 BUCKETS;

ANALYZE TABLE mytable DROP HISTOGRAM ON mycol1;

Checking that automatic statistics gathering is configured...

SELECT @@innodb_stats_auto_recalc;

SELECT @@innodb_stats_persistent;

Both values should be set to 1.

MySQL samples a set number of index pages (default=20) to estimate cardinality.

SELECT @@innodb_stats_persistent_sample_pages;

Increasing the value makes ANALYZE more accurate but causes it to generate more I/O.

The default values can be overridden in CREATE TABLE and ALTER TABLE statements.

FORCE INDEX

SELECT mycolumn

  FROM mytable

 FORCE INDEX (myindex)

 WHERE mycolumn = 'myvalue';

SERVER_COST

TODO. This is an unordered dump of information at this point...

USE mysql

SELECT *

  FROM server_cost;


+------------------------------+------------+---------------------+---------+---------------+| cost_name                    | cost_value | last_update         | comment | default_value |+------------------------------+------------+---------------------+---------+---------------+| disk_temptable_create_cost   |       NULL | 2020-01-29 09:37:41 | NULL    |            20 || disk_temptable_row_cost      |       NULL | 2020-01-29 09:37:41 | NULL    |           0.5 || key_compare_cost             |       NULL | 2020-01-29 09:37:41 | NULL    |          0.05 || memory_temptable_create_cost |       NULL | 2020-01-29 09:37:41 | NULL    |             1 || memory_temptable_row_cost    |       NULL | 2020-01-29 09:37:41 | NULL    |           0.1 || row_evaluate_cost            |       NULL | 2020-01-29 09:37:41 | NULL    |           0.1 |+------------------------------+------------+---------------------+---------+---------------+6 rows in set (0.00 sec)

Bibliography