I've bene trying to find info on how efficient queries are for the SET
datatype, specifically, how does
MySQL use indexes for SET column types.
I have looked in the O'Reilly MySQL Reference Manual, and at mysql.com and
have found effectively nothing on this subject.
For example, if i have a table :
CREATE TABLE some_table (
...
Set_column SET("A","B","C","D","E"),
...
KEY set_key(Set_Column)
);
i'm wondering how this column is indexed. I understand that the values
stored are bit representations, i.e.
if i store "A,D", the value stored is 01001 = 9
so i'd assume that the index is on the numeric values like above. So, If I
want to find all rows where
Set_column contains a "C" = 00100 = 4
SELECT * FROM some_table WHERE FIND_IN_SET("C", Set_column) > 0;
for instance, the MySQL engine couldn't just look in the index for all
columns with the (effective) value 4,
because "C,D" = 01100 = 12 would also contain a "C".
In fact, exactly half of the possible numeric values would represent sets
with a "C" in them.
It doesnt seem reasonable that the query engine would look at half of the
index values, so i dont really see how an index on a SET column would ever
be particularly useful.
I havent used sets much, in faact i've intentionally stayed away from them,
but i have an application where SET column types seem to make a lot of
sense, but i want to figure out the indexing issue before implementing it.
The specific application has to do with parsing lines of text and
identifying certain parts of the text based on keywords. So, I'd have a
table that has a "keyword" column (CHAR) , and a "type_of_keyword" column
(SET).
This is because some keywords could indicate multiple things, for instance,
if parsing names, "Thomas" could be a first name or a last name,
which one would need to be determined by context on a case-by-case basis,
but I need to be able to quickly decide what types of keyword
"Thomas" could be. Thats easy, but if i want to get all keywords that
represent last names, it seems that a table scan would need to be performed,
and
trying to use an index would be pointless.
Note that this example is contrived, as there would be 100,000+ last names,
etc.
But the example indicates the (perceived) problem.
Any insight into how indexes work in relation to SET columns would be
appreciated.
thanks
sean peters
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
