MYSQL OPTIMIZER HINT SERIES

This is 128k of data to calculate stats for, let’s say, 100G index. To calculate statistics, InnoDB performs a lookup into 8 (yes, eight!) index pages. In addition to this, table statistics were updated when either 1/16th or 2 billion rows were modified in a table. But it also happened when SHOW TABLE STATUS, SHOW TABLES or SHOW INDEX were executed. It happened when someone explicitly executed ANALYZE TABLE or at the first time table was opened. Historically index statistics were recalculated from time to time. Let’s first see how InnoDB statistics work and how we can change it.

The optimizer makes decisions about the best index for a query, and this is based on index statistics provided to it by the InnoDB engine. One of the ways we can influence the way a query is going to be executed is by using index hints.

MYSQL OPTIMIZER HINT SERIES

Our previous posts in the DBA series include Using EXPLAIN to improve SQL Queries, Database Indexing, Deep Dive pt-query-digest, Analyzing SQL Workload with pt-query-digest, Query Tuning Process, Configuration Tuning, Live Migration using MySQL Replication, Database Upgrades, Replication Topology Changes, Schema Changes, High Availability, Backup & Restore, Monitoring & Trending. This is the fourteenth installment in the ‘Become a MySQL DBA’ blog series. (Note that in MySQL 5.7, a lot of work has been done in order to improve this – users can modify the cost of different types of operations.)

However, it is possible to impact how a query will be executed, and this is the topic of today’s blog. For instance, disk access may have different costs depending on the type of storage used – SSD drives will have quicker access times than spindles, and can perform more operations in a given time. The optimizer makes decisions based on statistics and some fixed costs per operation, but it does not understand the differences in hardware. In some cases, it might not have enough information about the data and plan queries in a non-optimal way. It usually does a great job, but not all the time. MySQL uses a cost-based optimizer to determine the best way to execute a query.

Mysql optimizer hint