Dynamic range access (aka “Range checked for each record” since that is what EXPLAIN will say in the ‘Extra’ column) is one of the big mysteries to MySQL users. The reason is that it is used for queries that are less common, such as queries with non-equality join predicates.…
All posts by Jørgen Løland
The MySQL Optimizer Cost Model Project
You may not be aware of this but the foundation that the MySQL optimizer builds on when choosing a query plan – the cost model – is for the most part very old. At least in tech terms.
Much of it was written in another millennium, at a time when “Forest Gump” and “Titanic” won Oscars and “Baywatch” was the big thing on TV.…
A new dimension to MySQL query optimizations – part 2
This is “A new dimension to MySQL query optimizations – part 2”. If you didn’t read part 1 already I suggest you skim through it before reading on.
To summarize, the problem at hand is this: Given a query with a join between two or more tables, the MySQL optimizer’s mission is to find the best combination of join order and access method so that the response time becomes as low as possible.…
A new dimension to MySQL query optimizations – part 1
It’s not radical to claim that one of the most important tasks of any DBMS query optimizer is to find the best join order for the tables participating in a query. Is it, e.g., better to read country or city first in
|
1 2 3 4 |
SELECT * FROM country JOIN city ON country.id=city.countryid WHERE city.population > 1000000 AND country.region="EMEA" |
employee or department first in
|
1 2 3 4 5 |
SELECT * FROM employee JOIN department ON employee.dept_no=department.dept_no WHERE employee.first_name="John" AND employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01" AND department.location="Paris" |
If the optimizer gets this wrong, the resulting response time may be disastrous (or hilarious, depending on your sense of humour).…
You asked for it: new default for eq_range_index_dive_limit
In MySQL 5.6 we made it possible to estimate the number of fetched rows for queries with many equality predicates (e.g., many values in the IN clause). The new way of estimating fetched rows kicks in if the number of equality predicates exceeds the value of eq_range_index_dive_limit, which has a default of 10.…
FAQ: InnoDB extended secondary keys
MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I’ll try to get rid of the confusion once and for all.…