I’m facing some very confusing results here, so I’ll try to articulate them as well as I can:
I have a table with 30,000 rows, each timestamped, and categorised into 1 of 2 possible categories.
The timestamp column (which is actually an integer - it’s unixtime) has an index on it.
And the category column is of type ENUM(‘A’,‘B’), and also has an index on it.
I want to retrieve the most recent 50 rows, of a specific category (either ‘A’ or ‘B’).
So the query options are:
SELECT * FROM mytable WHERE category & 1 ORDER BY stamp DESC LIMIT 60
(this takes 0.01s to complete and is the best bet)
SELECT * FROM mytable WHERE category = 1 ORDER BY stamp DESC LIMIT 60
OR
SELECT * FROM mytable WHERE category = 'A' ORDER BY stamp DESC LIMIT 60
(both these options take 0.8s because for some reason they do not use the index and instead rely on filesort)
First of all, I can’t understand why the latter 2 fail to use the index and hence perform very poorly.
But where it gets even more confusing is when I try to query the 50 most recent records from category ‘B’. There are 0 records currently in category ‘B’, nonetheless the result now becomes:
SELECT * FROM mytable WHERE category = 'B' ORDER BY stamp DESC LIMIT 60
OR
SELECT * FROM mytable WHERE category = 2 ORDER BY stamp DESC LIMIT 60
(these become the best option, and take 0.0s to complete)
While:
SELECT * FROM mytable WHERE category & 2 ORDER BY stamp DESC LIMIT 60
becomes the bad option, taking 1.74s because it now fails to use the index.
So to summarise: Using the & comparison operator uses the index correctly when querying records which do exist, while the = operator uses the index correctly when searching for records which do not exist.
I’m really stuck here. Anyone have any ideas?