MySQL using index issue

Question

I have a big table which has around 7 million records. (MySQL)

Columns go like;

id | atype | textbody | .... 

id is primary key, atype is index

when I run

select * from tablename where `atype`='doit' 

it uses atype index. (there are 1.7 million doit rows in the table)

but when I run this query

select * from tablename where `atype`='doit' or `atype`='payment'

it doesn't use the index. I only says possible_index is atype. (there are 168 payment rows in the table)

is there any explanation of this behaviour?

If i run this query;

select * from tablename where `atype`='paymentfailed' or `atype`='payment'

It uses atype index.

so whenever I use 'doit', it doesn't use atype index

Answer 1

MySQL is rather finicky about indexes. I think it is smarter about IN rather than OR:

select *
from tablename
where `atype` in ('doit', 'payment');

If that doesn't work, then use union all:

select *
from tablename
where `atype` = 'doit'
union all
select *
from tablename
where `atype` = 'payment';