Re: Improve performance of a query with join and order by

[Rick James]

I can only guess without the SHOW CREATE TABLE.

SELECT  t1.*
    FROM  table1 t1
    JOIN  table2 t2 ON t2.a = t1.field1
      AND  t2.b = '83'
      AND  t2.c = '66'
    WHERE  t1.field3 IN ( 6 )
      AND  t1.field4 = '0'
    ORDER BY  t1.field12
    LIMIT  5000

These _compound_ indexes _may_ help:
On t2:
INDEX(b, c) -- or in the opposite order
INDEX(a, b, c) -- in this order
On t1:
INDEX(field4, field3, field12) -- in this order
INDEX(field1, field4, field3, field12) -- in this order

Also, since I don't know the frequency of the values in question, I cannot predict which index(es) would be most useful. (Or, for that matter, would be ever used.)

Can you show us the EXPLAIN.