List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 28 2003 1:55am
Subject:Re: enum vs char
View as plain text  
At 6:13 +0530 4/28/03, gamin wrote:
>I have one field (indexed) that can take 66 different values in a 
>table of 500,000 rows. Earlier i had it set to char(3) and queries 
>like
>
>select * from my_table where charfield like '3%' order by charfield limit 100;
>
>would showup in about 300 ms. I changed the field to not null enum 
>(with the 66 values) and an identical query takes about 5500 ms. Is 
>this sort of behaviour expected ??

I wouldn't say it's surprising.  ENUM values are represented internally
as integers, so the LIKE pattern-match involves conversion of the values
to string form so that they can be compared to the pattern.  Also, when
you had charfield represented as a string, if you had that column indexed,
LIKE can use the index if the pattern begins with a literal value, as
yours does.

>
>Thank you and regards
>
>G


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
Thread
enum vs chargamin28 Apr
  • Re: enum vs charPaul DuBois28 Apr
  • Re: enum vs chargamin28 Apr