Re: What is the best way to do attribute/values?

Hi,

On Aug 25, 2004, at 4:22 AM, Mark Kirkwood wrote:

> select
> pav1.person_id
> from
> person_attributes_vertical pav1
> where
> ( pav1.attribute_id = 1
> and pav1.value_id in (2,3))
> or ( pav1.attribute_id = 2
> and pav1.value_id in (2,3))
>

[...]

Why not combine attribute_id and value_id? Then you have nothing but an OR (or IN).

It should, AFAICS, give you much better selectivity on your indexes:

There will be a lot of attributes with the same ID; there will also be a lot of attributes with the same value. However, there should be much less attributes with a specific combination of (ID/Value).
Right now I think it will be very hard to determine which field has a better selectivity: attribute_id or value_id.

The combined attribute/value field could be an int8 or so, where the upper 4 bytes are for attribute_id and the lower 4 bytes for value_id.
Depending on the number of attributes and possible values a smaller datatype and / or a different split can be made. A smaller datatype will result in faster access.

What difference does that make?

regards,

--Tim