[MySQL] SELECT query is slow (no index needed), why is it so slow?

Question

I have a table with over 1 million entries.

The problem is with the speed of the SELECT queries. This one is very fast:

SELECT * 
  FROM tmp_pages_data 
 WHERE site_id = 14294

Showing rows 0 - 29 (1,273,042 total, Query took 0.0009 sec)

And this one is very slow:

SELECT * 
  FROM tmp_pages_data 
 WHERE page_status = 0

Showing rows 0 - 29 (15,394 total, Query took 0.3018 sec)

There is an index on the id column only, not needed in any of the selects. So there is no index on site_id or page status.

The 0.30 seconds query is very disturbing, especially when there are thousands requests.

So how can this be possible? What can I do to see what's slowing it down?

Answer 1

What can I do to see what's slowing it down?

It's quite obvious what is slowing it down - as you've already pointed out you don't have an index on the page_status column, and you should have one.

The only surprise is that your first query is so fast without the index. Looking at it more closely it seems that whatever client you are running these queries on is adding an implicit LIMIT 30 that you aren't showing in your question. Because there are so many rows that match it doesn't take long to find the first 30 of them, at which point it can stop searching and return the result. However your second query returns fewer matching rows so it takes longer to find them. Adding the index would solve this problem and make your query almost instant.

Short answer: add an index on the column page_status.

Answer 2

Ok, from our discussion in the comments we now know that the db somehow knows that the first query will returns all rows. That's why it's so fast.

The second query is slow because it doesn't have an index. OMG Ponies already stated that a normal index won't work because the value set is too small. I'd just like to point you to 'bitmap indexes'. I've not used them myself yet but they are known to be designed for exactly this case.