ENUMs, indexes = slow queries? (mysql)

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?

hmm, yeah, that is very odd. i wonder if MySQL handles indexed ENUMs differently (buggy?).

are you only going to have A and B type values or not? if so, maybe you could use a TINYINT (or bigger, if needed) and store 0 and 1 instead?

MattR, DaveMaxwell, etc., any ideas? :wink:

BTW, if you’re not searching on the stamp column, you might as well lose it as a seperate index and make a multi-column index on category and stamp together. maybe you already know that, though. :slight_smile: then, i think, MySQL could use the index for the `ORDER BY stamp’, too.

& is not a comparison operator. & performs bitwise arithmetic on the column…

When you perform column AND 1 it just results in the column, so it is fast (doesn’t have to do any math). column AND 2 requires work.

But remember that indexes on columns which are highly repetitive (e.g. index on a ‘sex’ column of M/F, your enum col) are not very useful in query performance (what is called ‘not very selective’). MySQL will escalate to a table-scan any way after 30% row scans (I think) since it is far cheaper than trying to look at the index.

MySQL is limited to using a single index on a table per query, so having two will not help.

This might, provided it can sort on the inner cols of an index:
CREATE INDEX whatever_idx ON yourtable( category, stamp desc )

Originally posted by MattR
[B]& is not a comparison operator. & performs bitwise arithmetic on the column…

When you perform column AND 1 it just results in the column, so it is fast (doesn’t have to do any math). column AND 2 requires work.[/B]

since ENUMs can be compared with their string values or numerically, i thought & was acting as a comparison operator. if you had an ENUM(‘foo’, ‘bar’, ‘apple’) i thought `category & 1’ would return all rows that have odd number ENUM values (‘foo’ or ‘apple’ in this case). but then, i don’t really see how it could use the category index for a bitwise AND.

z0s0, you didn’t say, which index is being used in each query (that would be the `key’ column from EXPLAIN)?

It sort of will be comparison, in that it will return either true or false for rows, e.g.:

WHERE category & 1
turns into:
WHERE (0 or positive int)

So it will do weird things.