Skip to main content
Database Administrators

Return to Question

formatting; coded elements; added tag;
Source Link
John K. N.
John K. N.
  • 18.3k
  • 14
  • 54
  • 115

iI have three tables defined:

C, CT, T

  • C
  • CT
  • T

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

There are secondary indices defined on T.colT.col and on CC.pop.pop

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, because of the ORDER BYORDER BY and LIMITLIMIT clauses).

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

  • using FORCE INDEX
  • query optimizer hints

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.

Any ideas?

For those interested (not necessary to answer question)

for those interested (not necessary to answer question): theThe calculations as to why said query plan would be optimal: pseudo in pseudo-code:

evenEven if the % of T rows with T.col == 8T.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).

i have three tables defined

C, CT, T

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

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

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)

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

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: pseudo-code:

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)

I have three tables defined:

  • C
  • CT
  • T

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

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

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.

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

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:

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).

Source Link
ktn
ktn
  • 1

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

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: 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)