Analytical Queries in MySQL
summary
Benchmarks use TPC‑H queries and compare MySQL 5.7, MySQL 8.0, MariaDB and PostgreSQL on the same data; tpch query 21 runs ~90% faster in 8.0 after optimizer and semijoin fixes, query 17 drops ~80% by using window functions, and some queries fall back slower in 8.0. Disk‑bound runs show batch key access cuts IO by >10× on several queries. Using hash join (hiding indexes) or larger join buffers yields two‑thirds reductions or more; parallel execution on 32 cores shows >16× speedups.
AI-written summary · about
chapters (19)
- 0:00Intro
- 1:09OLTP vs OLAP
- 2:12TPC-H vs DBT-3
- 3:56TPC-H Schema
- 4:22TPC-H Results (Scale Factor 10)
- 5:35Configuration Details
- 6:53MySQL 8.0 vs MySQL 5.7
- 9:18Disk-Bound Queries
- 11:01Batched Key Access (BKA)
- 13:07Effect of Join Buffer Size
- 13:45How to Tune Your Complex Queries
- 14:54Full Table Scan vs Index Range Scan
- 15:46Optimizer Hints - Change Join Order
- 17:04MySQL Statistics - Improve Accuracy
- 19:47Rewriting Queries
- 22:20Hash join vs BKA
- 22:49When to Use Hash Join?
- 23:52Impact of Join Buffer Size
- 26:14MySQL Wishlist for Complex Queries
