I have a query that goes such:
bankNumber and BIC are both indexed.
SELECT * FROM `bank1` WHERE `bankNumber` <= 200000 or `BIC` = 'Berlin'
By using the EXPLAIN, it uses both of the keys in possible_keys. I read in an article that one of the things that not to do when queries for indicies is to use OR.
I am tasked to optimize this query so that only one key gets used in the execution strategy.
What should I do in this case?
Things that I’ve tried:
Obviously went for AND, that did give me the desired result from EXPLAIN, but the data results are completely different.
>Solution :
AND obviously will produce different results. The only way to use indexes would be to union:
select bank1.* from (
select id from bank1 where bankNumber <= 200000
union distinct
select id from bank1 where BIC='Berlin'
) ids
join bank1 on bank1.id=ids.id
But I question your entire goal; is the query in fact too slow? It doesn’t seem like you would have enough bank rows that a full table scan would be that bad.