On Thu, Mar 20, 2003 at 05:06:09PM +0100, Benjamin Pflugmann wrote:
> Hi.
>
> On Thu 2003-03-20 at 15:52:25 +0100, [EMAIL PROTECTED] 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
---------------------------------------------------------------------
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
