Hi,
I have a question about indexing a column of type SET. It seems that
generally the index is not too useful, since it always requires some
sort of expression to get what you want out of the set, and therefore
the index won't be used.
Example:
> create table FOO (BAR set('a','b','c','d'));
> create index IDXBAR on FOO(BAR);
Let's say I want to select the rows where BAR contains the set value 'a'.
> select count(*) from FOO where FIND_IN_SET('a',BAR) > 0;
The above does not use the index since FIND_IN_SET() is an expression.
> select count(*) from FOO where BAR & 1;
Again, BAR & 1 is an expression, no index used.
> select count(*) from FOO where BAR like 'a%';
The above seems to work, although this approach won't work for many
cases. What if I want to find where the column contains 'b' and 'd' ?
> select count(*) from FOO where BAR in (1,3,5,7,9,11,13,15);
This works but making a list is not practical, and the list size would
grow exponentially as you add set members.
So is there a way to do what I'm trying to do? If there is no way, how
difficult would it be to add this to MySQL?
I'm not sure of the best way to go about it, but one idea is internally
replace "FIND_IN_SET('a',BAR)" with "BAR IN (1,3,5,7,...)", ie. let
MySQL handle the calculation work so a SET can use an index in a
practical case.
Monte
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
