Saturday, 10 August 2013

mysql uses primary key instead of index

mysql uses primary key instead of index

I have a pretty large table with a few million rows:
ID (primary)
countrycode
status
flag_cc
I tried the following sql statement, but it was quite slow:
SELECT id, countrycode, status, flag_cc FROM table WHERE ID>=200000 AND
countrycode=3 AND status=1 AND flag_cc=0
So I thought it would be a good idea to add an index to fasten that query up:
ADD INDEX myindex(id, countrycode, status, flag_cc)
Then I queried:
EXPLAIN SELECT id, countrycode, status, flag_cc FROM table WHERE
ID>=200000 AND countrycode=3 AND status=1 AND flag_cc=0
but mysql wants to use the primary key instead of my key. So I used FORCE
INDEX and compared the primary key to my key.. Sadly the primary key was a
lot faster.
How could that be? And is it ever possible to optimize that query if even
the primary key is too slow?

No comments:

Post a Comment