Category: Troubleshooting

Performance, Troubleshooting

Improved JOIN Order by Taking Condition Filter Effect into Account

One of the major challenges of query optimizers is to correctly estimate how many rows qualify from each table of a join. If the estimates are wrong, the optimizer may choose a non-optimal join order. Before MySQL 5.7, the estimated number of rows from a table only took into account the conditions from the WHERE …

Troubleshooting

Tribulations of a MySQL Evangelist Generic Partitions & Native Partitions

As you may already know,  since MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is completely removed in MySQL 8.0. So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines …

Performance, Troubleshooting

What to Do When the MySQL Optimizer Overestimates the Condition Filtering Effect

In my previous blog post, I showed an example of how the MySQL Optimizer found a better join order by taking into account the filtering effects of conditions. I also explained that for non-indexed columns the filtering estimate is just a guess, and that there is a risk for non-optimal query plans if the guess …