z0s0 — 2002-06-04T02:47:52-04:00 — #1
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
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
SELECT * FROM mytable WHERE category = 2 ORDER BY stamp DESC LIMIT 60
(these become the best option, and take 0.0s to complete)
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?
dr_larry_pepper — 2002-06-04T04:18:20-04:00 — #2
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?
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. then, i think, MySQL could use the index for the `ORDER BY stamp', too.
mattr — 2002-06-04T15:57:00-04:00 — #3
& 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 )
dr_larry_pepper — 2002-06-04T19:00:53-04:00 — #4
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)?
mattr — 2002-06-04T19:15:02-04:00 — #5
It sort of will be comparison, in that it will return either true or false for rows, e.g.:
WHERE category & 1
WHERE (0 or positive int)
So it will do weird things.