MySQL8.4-What makes a MySQL query slow?

Section 2. What makes a MySQL query slow? This is one of the most important concepts in this course. A slow query is a symptom, not a diagnosis. There are six common categories of bottlenecks. 1. Inefficient data access. MySQL examines millions of rows to return just a few records because it lacks a suitable index or chooses an expensive access path. The typical clue is type equals all in traditional explain output to inefficient SQL and joins poor join order, correlated sub queries, non sargable predicates, or excessive intermediate rows increase execution work. The typical clue is unexpectedly high actual row counts or loop counts. 3. CPU and I/O bottlenecks. Sorting, aggregation, large scans, and random data access can consume CPU or generate substantial storage activity. The typical clue is high CPU utilization, storage latency, or read activity that correlates with slow SQL. 4. Locking and concurrency. A query may be waiting for another transaction instead of actively processing rows. The typical clue is lock waits, blocked transactions. 5. Resource pressure. Insufficient memory, buffer pool, misses, storage limitations, or CPU saturation can slow down otherwise reasonable SQL. The typical clue is resource saturation that coincides with increased query latency. 6. Application and connection overhead. Too many database round trips, connection pool, exhaustion, or inefficient application behavior can increase end-to-end response time. The typical clue is high application latency. Even when individual SQL statements are fast, here is an important point. A query that takes five seconds is not necessarily spending five seconds executing SQL. It may be waiting for a Waiting for a connection. or experiencing delays elsewhere in the application, so before attempting any optimization, learn to distinguish execution time, wait time, and end-to-end application latency.