Re: query/table design help

On Tuesday 05 August 2003 15:03, Ara Anjargolian wrote:
> I have a table
> permissions
> with the fields (party_id integer, permission varchar, key_name varchar,
> key_value integer)
> for which I need to a query to see if a person has permission to carry out
> a particular action.
> The query looks like:
> SELECT 1
> FROM permissions
> WHERE party_id in (4, 7, 11, 26)
> AND
> permission = 'permission8'
> AND
> ((key_name = 'keyname8' AND key_value = 223) OR
> (key_name = 'keyname1' AND key_value = 123) OR
> (key_name = 'keyname5' AND key_value = 212) OR
> (key_name = 'keyname7' AND key_value = 523) OR
> (key_name = 'keyname0' AND key_value = 123) OR
> (key_name = 'keyname10' AND key_value = 400));
>
> would a permissions(party_id, permission) index work best here?
> or should I index all 4 columns?
>
> Also,
> Another alternative is to combine the key_name and key_value fields into a
> varchar
> field key (e. g. 'keyname8=223'), in which case the equilalent query would
> just check
> 1 field 6 times instead of having 6 ANDstatements.
>
> I expect the table to have about 1 million rows at the most, and I need
> this query to run as fast
> as possible since it will be run many, many times.

I would suggest a 3 column table with party id, action and permission. Index
on partyid and action.

If table is static enough clustering should help.

But this is one of many possible ways to design it. There could be other
details that can affect this decision.

Shridhar