Optimizing a certain query-CodePudding

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.

CodePudding user response：

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.