On Thu, Mar 20, 2003 at 05:06:09PM +0100, Benjamin Pflugmann wrote:
> 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:
SET values are sorted numerically. NULL values sort before non-NULL SET
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;
which is exacly what I am doing.
The Moon is Waning Gibbous (93% of Full)
tty.nl - 2dehands.nl: 80117