# It's possible optimize query with only index scan?

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
FROM A
INNER JOIN B using (id_a)
WHERE (A.field1 = 123 OR A.field2 = 123) AND B.field3 = 1234
LIMIT 1;
``````

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
• could you post the definitions for the indexes on these tables? – Jonathan Fite Mar 2 '18 at 13:31
• Please, no hand waving. Provide `SHOW CREATE TABLE` for each table. – Rick James Mar 7 '18 at 3:06

``````B:  (field3, id_a, field4)