Mastering MySQL Slow Query Optimization: Practical Strategies from Ctrip

Background

Slow queries are SQL statements whose execution time exceeds a predefined threshold, leading to long runtimes, high server resource consumption, and delayed business responses. Rapid growth of hotel data and large‑scale SQL‑Server‑to‑MySQL migrations have caused a sharp increase in slow‑query volume and daily alerts, making MySQL slow‑query governance urgent.

Slow Query Governance Practice

1. SQL Release Process Optimization

The previous fast‑track release allowed low‑quality SQL to be deployed, increasing alerts and rollbacks. A new SQLReview step screens potential slow queries before production, ensuring that only DBA‑approved SQL reaches the live environment.

The platform provides automated review, but complex hotel scenarios and varying developer expertise mean its suggestions are not universally adopted.

2. Understanding Query Statements

2.1 SQL Flow and Optimizer

A typical SQL execution follows these steps:

SQL syntax cache (QC)

Parsing

Generating execution plan

Executing the query

Returning results

Slow queries usually occur during the “Execute query” phase; reading the execution plan helps pinpoint performance issues.

2.2 Execution Plan

Running EXPLAIN before a query displays the plan in a tabular format. Column meanings follow the MySQL documentation.

3. Optimizing Slow Queries

3.1 Index‑Related Issues

Missing Index

select * from hotel where name='xc';

Adding an index on name makes the optimizer use it:

Invalid Index (Index‑Merge Failure)

Patterns that cause index loss include LIKE, OR, functions, or mismatched parameter types. Examples:

explain select * from hotel where name like '%酒店%';
explain select * from hotel where name like '%酒店%' or Bookable='T';
explain select * from hotel where name <> '酒店';
explain select * from hotel where substring(name,1,2)='酒店';

A type mismatch can also disable an index:

create table t1 (col1 varchar(3) primary key) engine=innodb default charset=utf8mb4;

Comparing col1 with a numeric value forces implicit conversion, causing a full table scan.

Composite Index Issues

If the WHERE clause does not follow the left‑most prefix rule, the composite index becomes ineffective.

alter table hotel add index idx_hotelid_name_isdel(hotelid, name, status);

Queries that satisfy the left‑most rule use the index:

explain select * from hotel where hotelid=10000 and name='ctrip' and status='T';
explain select * from hotel where hotelid=10000 and name='ctrip';
explain select * from hotel where hotelid=10000;

Queries that do not start with the leftmost column cannot use the index:

explain select * from hotel where name='ctrip' and status='T';
explain select * from hotel where name='ctrip';
explain select * from hotel where status='T';

Data Distribution & Volume

Uneven data distribution or very small tables may lead the optimizer to favor a full table scan.

Queries Without WHERE

Full‑table scans for updates or selects on large tables are dangerous; batch processing is recommended.

3.2 Query Frequency

Incorrect loop termination in application code leading to endless loops.

High‑concurrency scenarios such as flash sales.

Jobs that pull full data sets without proper pacing.

Cache breakdown or cache invalidation after deployment.

3.3 Bad Writing Practices

Pagination

Using LIMIT offset, rows becomes slower as offset grows because the engine must scan and discard rows. SELECT * FROM table LIMIT offset, rows; Deep pagination leads to increasing scanned rows and degraded performance.

Instead, use range‑based fetching or incremental IDs to avoid large offsets.

Max/Min Queries

When data distribution is skewed, MAX or MIN can be very slow.

explain select max(id) from hotel where hotelid=10000 and status='T';

Adding a composite index can reduce scanned rows dramatically:

alter table hotel add index idx_hotelid_status(hotelid, status);

With index covering, EXTRA shows Select tables optimized away, meaning the query can be satisfied entirely from the index.

Order‑by Optimization

explain select id from hotel where hotelid=10000 and status='T' order by id desc limit 1;

Even though it uses an index scan, MySQL optimizes the LIMIT to avoid a full scan.

Sorting & Aggregation GROUP BY and ORDER BY can create temporary tables and filesorts, which are costly on large data sets.

Creating an index that satisfies the sort order avoids filesort and temporary tables:

alter table hotel add index idx_name_hotelid(name, hotelid);

In some cases, adding ORDER BY NULL cancels implicit sorting, but MySQL 8.0 no longer requires this.

3.4 Resource‑Related Issues

Lock Contention

Hot tables can suffer lock waits, leading to slow queries. Recommendations include cautious use of FOR UPDATE, ensuring DML statements use indexes, and reducing concurrent modifications on the same rows.

Network Instability

Network jitter between client and server can manifest as slow queries.

Hardware Limits

High CPU utilization and saturated disk I/O also cause performance degradation.

Conclusion

Slow‑query governance is a long‑term effort that should start before timeout errors appear. Establishing a daily, systematic process from the outset is essential to control growth. However, database‑level tuning alone cannot eliminate all slow queries; many stem from business logic and application design, requiring developers to write efficient SQL, improve application strategies, and receive proper training.