Force MySQL to use specific index on ORDER BY clause with LIMIT to satisfy join

Question

MySQL 8.0.30

I have three tables defined:

• C
• CT
• T

CT is a linking table (many-to-many between C & T)

The query I'm trying to optimize is of the following kind:

SELECT C.*, T.*
FROM C JOIN CT ON C.id = CT.cid JOIN T ON CT.tid = T.id
WHERE T.col = 8
ORDER BY C.pop
LIMIT 20

There are secondary indices defined on T.col and on C.pop.

The optimal query plan is to satisfy the query with the following join-order:

C (*using C.pop_idx*)
    join CT (c.id == ct.cid)
    join T (ct.tid == t.id)
    filter (T.col == 8)

There is no doubt that this is the optimal query plan, because of the ORDER BY and LIMIT clauses.

What MySQL chooses instead is:

CT (table-scan)
    join T (using T.primary)
    filter T.col == 8
    join C (using C.primary)

I've tried everything I can think of:

• using FORCE INDEX
• query optimizer hints

(/* JOIN_ORDER(C, CT, T) */), /* INDEX(C, C.pop_idx) */, etc, etc

But to no avail.

The frustrating thing is that I've seen query plans where MySQL is smart enough to do exactly what I want it to do, viz. use the index defined on the ORDER BY/LIMIT to "filter" the rows in the outermost table as the outermost loop of a nested loop IJ, but I simply can't induce it to do so in this case.

Any ideas?

For those interested (not necessary to answer question)

The calculations as to why said query plan would be optimal in pseudo-code:

for each row in C [using index C.pop_idx read 20 rows (or so) in order at a time]
     for each matching row in CT (CT.cid == C.id) (using CT primary)
          for each matching row in T (CT.tid == T.id) (using T primary)
              if T.col == 8:
                   emit C.*, T.*
    
              if total emitted rows == 20:
                  done

Even if the % of T rows with T.col == 8 is small (say, 1%), on average only 20 * 100 C rows would need to be read (which is way less than all the rows in CT).

Answer 1

CT should have only these two indexes:

PRIMARY KEY(cid, tid),
INDEX(tid, cid)

Add these if necessary

C: INDEX(pop)
T: INDEX(cpl)

The Optimizer may decide to filter on T.cpl first, then reach for C. Or it may decide to avoid sorting by starting with C.pop.

Please provide SHOW TABLE STATUS (for table sizes), SHOW CREATE TABLE (for other indexes and datatypes), plus a plain EXPLAIN SELECT ...

If you don't need all the columns, consider not using .*.