Which is faster: LIMIT 1 or LIMIT 20?
Presumably, fetching less rows is faster than fetching more rows.
But for 16 years (since 2007) the MySQL query optimizer has had a “bug”† that not only makes LIMIT 1 slower than LIMIT 20 but can also make the former a table scan, which tends to cause problems.
This happened last week where I work, and although MySQL DBAs are familiar with this bug, I’m writing this blog post for developers to more clearly illustrate and explain what’s going on and why because it’s really counterintuitive.
Efficient MySQL Performance teaches you what MySQL performance is and how to get it. Based on nearly 20 years of real-world experience, Efficient MySQL Performance clears a path through the complexity, pitfalls, and misunderstandings of MySQL performance. Follow it and you will understand MySQL performance like an expert.
"Daniel does a great job of showing how to drive to the fastest resolution without getting lost in guesswork. He also covers InnoDB row locking in a detailed yet digestible form."
—Vadim Tkachenko, CTO, Percona
"This book is different from other MySQL books—it focuses on what matters most: query response time. A must-have for MySQL users!"
—Frédéric Descamps, MySQL Community Manager, Oracle Belgium
- The Future of MySQL Schema Change: Spirit
- Deferred Join: A Deep Dive
- Announcing Finch: A New MySQL Benchmarking Tool
- Are Aurora Performance Claims True?
- Announcing Blip: A New MySQL Monitor
- The First Design
- COMMIT Latency: Aurora vs. RDS MySQL 8.0
- Blip: A New Open Source MySQL Metrics Collector
- MySQL IOPS for Reads and Surprsies
- Is MySQL in the Cloud the End of the DBA?
- How Not to Use MySQL
- Mining the MySQL Performance Schema for Transactions
- Better Replication Heartbeats
- InnoDB Page Flushing Diagram
- NewDB: Fate of an Open Source Database
- I Don't Want to Shard (MySQL)
- Access Patterns for MySQL
- Performance Is Less
- MySQL EXPLAIN ANALYZE
- Configuring MySQL Query Metrics
- Efficient MySQL Performance
- MySQL LRU Flushing and I/O Capacity
- MySQL Data Locks: Mapping 8.0 to 5.7
- MySQL Password Rotation with AWS
- Query Metrics Requirements for MySQL
- What the Flush?
- 21 Parameter Group Values to Change in Amazon RDS for MySQL
- Missing Writes with MySQL GTID
- MySQL Threads Running
- Queries Effect Performance
- Fixing Ghosted GTIDs
- Response Time SLA
- Path and Dimension Metrics
- 3 Golang Error Types
- 3 Golang Channel Red Flags
- Component-based Software Design
- When MySQL Goes Away
- Thinking in Go
- Bootloader Design Pattern
- Database Scalability: Contention and Crosstalk
- Crash-safe MySQL Replication
- Tech Workers Are Good People
- Approaching a New Software Project
- Ideas, Leaders, and Engineers
- Five-Point Checklist for Excellent Software
- Designing Tools for Integration
- How To Test the Database
- Design Before Implementation
- Reading os/exec.Cmd Output Without Race Conditions
- Custom MongoDB Replica Set Write Concern