The Slow-Query Scenario

Some time after a feature went live, slow queries started appearing. Setting aside business-specific data compatibility handling and simplifying the statements, the slow queries fell into two basic types:

# case one
select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where (c.col_j like '%search%' or a.col_k like '%search%') and a.col_const = const_n
order by a.col_x desc limit 50
or
order by a.col_y desc, c.col_x desc limit 50;

# case two 
select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where c.id in list_c and a.col_count = const_n

order by a.col_x desc limit 50;
or
order by a.col_y desc, c.col_x desc limit 50;

At the time, table a had the indexes idx_const_n_col_x, idx_const_n_col_y, and idx_col_n. Tables a and b each had millions of rows, while c had tens of thousands.

The First Solution

The most obvious issue was a_col_k like %search%. Indexes in mysql follow the leftmost-prefix rule, so search% can be handled efficiently, but %search cannot. This requires a reverse index. ElasticSearch can handle this kind of full-text search, and since the existing system already used ES, that was the solution we chose. In brief, we maintained a mapping between a.col_k and a.id, then used full-text search in ES to quickly find a list of id values in es_col_k_id_map. The original sql became:

select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where (c.col_j like '%search%' or a.id in a_id_lists) and a.col_const = const_n
order by a.col_x desc , a.col_y desc limit 50;

Table c had only tens of thousands of rows and was expected to remain at roughly that scale as the business grew, so leaving c_col_j like %search% unoptimized was acceptable. However, similar logic was frequently needed in the business, and a simplified reverse-query solution had already been implemented in redis. We reused that code for further optimization.

For case two, the solution was to remove the join c operation first. The original statement became:

list_c = some_sql
select * from a join b on a.col_n = b.id
where (b.col_m in list_c or a.id in a_id_lists) and a.col_const = const_n
sorted by a.col_x desc , a.col_y desc limit 50;

We then added an index on col_m in b. After some basic testing, the first version went live.

The Second Solution

After deployment, slow queries were much less frequent, but they had not disappeared completely. A closer look at the slow-query logs in ELK revealed two remaining types:

# case one
select * from a join b on a.col_n = b.id
where a.col_const = const_n and b.col_m in list_b
order by a.col_y desc, a.col_x desc limit 50;

# case two
select * from a join b on a.col_n = b.id
where a.col_const = const_n and b.col_m in list_b order by a.col_x desc limit 50;

The first was relatively easy to solve by adding the index idx_col_const_col_y_const_x; the previous index idx_col_const_col_y could also be removed. For the second, running explain sql revealed that the MySQL optimizer sometimes chose idx_col_m first, because it scanned fewer rows than idx_const_col_x. However, since idx_col_m did not contain col_x, this still required fliter sorting afterward, making the query very slow. Using Focus use idx_col_const_col_y_const_x to force use of idx_col_const_col_y_const_x resolved this. There was one small detail: both forms of order by could occur, so we needed to choose either idx_const_n_x or idx_col_const_col_y_const_x according to the order by clause.