MySQL Forums
Forum List  »  Newbie

SET Datatype Performance
Posted by: Juan Fernandez
Date: April 21, 2005 06:38AM

I have a “little” performance problem using the SET datatype.

I have a database with over 800,000 rows which have 34 columns which are enum(‘0’,’1’) to describe various categories.

So when I search for a certain category or categories the search would be like:

Select count(id) from my_table where cat1=’1’ and cat10=’1’ and cat25=’1’;

If cat1 is indexed and cat10 is not.

Select count(id) from my_table where cat=’1’;

would return data in less than a sec while

select count(id) from my_table where cat10=’1’

might take over 5-6 secs to return the value.

Since I can’t index all 34 category columns, due limitations of how many indexes I can create (there are other fields that required index besides the categories) I looked into how to do this more efficiently. I came up with the idea if using the set datatype. I created a test table

CREATE TABLE set_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cats SET('cat1','cat2','cat3',……'cat34')
);

I loaded it up with 800,000 values from my main table and searches like

Select count(id) from set_test where cats&1;

Would return the count in less than a second.

So far so good. So I added a new column to the main database with the ‘cats’ column, containing the same info I had in the set_test table. To my surprise:

Select count(id) from my_table where cats&1;

Would take over 6 secs to return the value.

Or course the table has 45 columns (including the old 34 category columns) instead of two, but does it really make that much of a difference? If so, what can I do to get a decent performance?


Thanks in advance,

Juan

Options: ReplyQuote


Subject
Written By
Posted
SET Datatype Performance
April 21, 2005 06:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.