I have the following tables:

A: (id_a, field1, field2) with 32M rows
B: (id_b, id_a, field3, field4) with 3M rows

All columns are indexed, except b.field4. A.id_a and b.id_b are PK.

And the following query:

SELECT B.field4
    INNER JOIN B using (id_a)
WHERE (A.field1 = 123 OR A.field2 = 123) AND B.field3 = 1234

I have 48k rows on table A that match with the condition A.field1 = 123 OR A.field2 = 123) and 1.8k on table B that match with B.field3 = 1234P.

The number of rows returned by the query without the limit is 74.

Explain shows that 1.8k rows will be analyzed.

The question is: can this query be optimized? I was assigned to a task to improve it. The only thing I think I can do is add field1 to table B and remove the OR field2 = 123 part.

  • Try " SELECT B.field4 FROM B WHERE B.field3 = 1234 AND (B.id_a IN (SELECT id_a FROM A WHERE A.field1 = 123) OR B.id_a IN (SELECT id_a FROM A WHERE A.field2 = 123)) " – Jonathan Fite Mar 2 '18 at 3:32
  • Same results. A little bit slower, but very small difference. Thanks! – Luís Henrique Faria Mar 2 '18 at 12:33
  • 1
    could you post the definitions for the indexes on these tables? – Jonathan Fite Mar 2 '18 at 13:31
  • 1
    Please, no hand waving. Provide SHOW CREATE TABLE for each table. – Rick James Mar 7 '18 at 3:06
B:  (field3, id_a, field4)
A:  (id_a)

The B index will be optimal and "covering". So, even if it touches 1.8K 'rows', they will be compactly sitting in the index.

Separate indexes on individual columns is not the same as a composite index.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.