List:General Discussion« Previous MessageNext Message »
From:harm Date:March 20 2003 4:26pm
Subject:Re: Index on a (homemade) SET?
View as plain text  
On Thu, Mar 20, 2003 at 05:06:09PM +0100, Benjamin Pflugmann wrote:
> Hi.
> 
> On Thu 2003-03-20 at 15:52:25 +0100, harm@stripped wrote:
> > 
> > I use a lot of SET-alike fields in my tables. The fields are used to store
> > properties and such; every bit stands for a specific value. The fields are
> > queries like 'where property_field & 1<<4' or 'where property_field
> & 1025'
> > if you look for more than one property.
> > 
> > Just your average SET behavoir and very convenient to use.
> > 
> > 
> > The only problem is these queries are slow. You need to do a tablescan to
> > find the matching records. Making a seperate table where you store an entry per
> > property is another option but even slower (makes a big difference after
> > even a few tens of thousend of rows).
> > 
> > Any hint / tips / ideas how to index a set-like field?
> 
> Maybe you should start by explaining why you don't use SETs? Whatever
> the reason is, I strongly suspect that it will influence the answer.

Because doing that work myself is easier. I can change the names the bits
represent without changing the table. Also, the values can have
different meaning depending on other settings.

> All what you described works fine with SETs and there is a chance that
> MySQL will optimize accesses to them better.

I`ll give it a try. But I doubt it will make a difference:

<mysql doc>

SET values are sorted numerically. NULL values sort before non-NULL SET
values. 

Normally, you perform a SELECT on a SET column using the LIKE operator or
the FIND_IN_SET() function: 
<...>
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

</mysql doc>

which is exacly what I am doing.

Thanks,

Harmen



-- 
                                The Moon is Waning Gibbous (93% of Full)
                                             tty.nl - 2dehands.nl: 80117
Thread
Index on a (homemade) SET?harm20 Mar
  • Re: Index on a (homemade) SET?Benjamin Pflugmann20 Mar
    • Re: Index on a (homemade) SET?harm20 Mar