MySQL Optimizer Hints and Histograms When the Plan Needs a Nudge
MySQL 8 gives you two tools PostgreSQL deliberately does not: histograms you create on demand with ANALYZE TABLE ... UPDATE HISTOGRAM on columns that have no index, and optimizer hints written inside the statement to force an index, a join order or a join algorithm. Hints are a supported escape hatch and a maintenance liability — they freeze a decision that was right for today's data. Reach for the histogram first, the hint only when you have proved the planner is wrong and cannot be corrected.
Questions this Concept answers
- Why can a histogram help a column that will never be indexed?
MySQL Histograms and Optimizer Statistics Guide
MySQL's optimizer decides how to run a query by guessing how many rows each filter will match, and on lopsided data with no index on the column it guesses very badly. This page shows how to build a h…