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
bytesfor value_id. 
Depending on the number of attributes and possible values a smaller datatype and / or a different split can be made. A
smallerdatatype will result in faster access. 

What difference does that make?

regards,

--Tim