MySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries

on YouTube ↗01h 30m

" summary

Indexes speed lookups but add write and storage overhead; histograms give the optimizer a cheaper distribution for non‑indexed columns (max 1024 buckets) and suit slowly changing fields. explain analyze actually runs the query so differences expose stale stats; visual explain and format=tree/json reveal hash joins (8.0.17/18) versus nested loop. Use invisible indexes to test removing indexes safely; use skip locked and no wait to avoid long waits on hot rows. multi-value indexes index JSON arrays. Upgrade off MySQL 5.6 (EOL Feb 5 2021).

AI-written summary · about

" chapters (24)

1. 0:00Intro
2. 2:32What Is This Session About?
3. 7:44The Optimizer Consider the optimizer the brain and nervous system of the system
4. 8:50The query optimizer evaluates the options
5. 10:46EXPLAIN Syntax
6. 11:36EXPLAIN Example
7. 15:24EXPLAIN ANALYZE - MySQL 8.0.18
8. 19:08Creating a table with a PRIMARY KEY (index) CREATE TABLE ti C1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
9. 20:38PRIMARY KEY This is an key for the index that uniquely defined for a row, should be immutable.
10. 23:06Multi-column index - last_name, first_name CREATE TABLE test INT NOT NULL
11. 24:39Hashing values - an alternative
12. 25:33Some other types of indexes Unique Indexes - only one row per value Full-Text Indexes - search string data
13. 28:25Functional Indexes are defined on the result of a function applied to one or more columns of a single table
14. 30:28Multi-value indexes
15. 31:39MySQL has Two Main Types of Index Structures
16. 38:45After Invisible Indexes
17. 47:03Two-types of histograms Equi-height: One bucket represents a range of values. This type of histogram will be created when distinct values in the column are greater than the number of buckets specified in the analyze table syntax. Think AG H-LM-TU-Z
18. 48:16Frequency Histogram - an Example
19. 49:43The statistics
20. 51:31Where Histograms Shine
21. 54:08With EXPLAIN ANALYSE
22. 56:30Locking Examples - Buying concert tickets
23. 58:03LOCK NOWAIT
24. 59:15Resource groups - setting and using CREATE RESOURCE GROUP Batch TYPE - USER VCPU = 23 - assumes a system with at least 4 CPUs THREAD_PRIORITY = 10

" related talks

• 35mMySQL Indexes and Histograms to Speed Up Your QueriesDavid Stokes · Data Con LA 2021
• 30mMySQL Indexes and Histograms to Speed Up Your QueriesDave Stokes · CakeFest 2022
• 46mSupercharge Your SQL with Indexes: a Practical IntroductionAlberto Schiabel · DevDays Europe 2024
• 36mFor performance, go to page 5: IndexesJachim Coudenys · Dutch PHP Conference 2024
• 49mIt’s Not Always About the Index: The Hidden Secrets of Query PerformanceAvishag Sahar · NDC Copenhagen 2025
• 21mWriting High Performance SQL StatementsTim Sharp · Percona Live MySQL Conference & Expo 2015
• 41mMySQL Query Optimization Best Practices and IndexingAlkin Tezuysal · Highload fwdays'18
• 39mMaking the most out of MySQLGabriela D'Ávila · Laracon EU 2016