Hi,

We are having performance problems, which lead to an overall database
architecture question.

We have a table A, each record of which can have a set of Setting
records. These setting records are basically just name-value pairs.

As you would expect, we modeled things as a basic parent-child
relationship with A.id as the foreign key for the Setting table.

However, we need to search for records in A which have a certain set
of settings eg setting1=a, setting2=b, setting3=c, setting4=d. The
setting names can change across queries, as can the number of settings
we need to match in a particular query.

The only way I have figured out to perform such a query is to
self-join the Setting table multiple times (once for each setting
value I want to query on). So for 7 settings we get:

SELECT * FROM A
INNER JOIN SETTING S1 ON A.ID = S1.ID
INNER JOIN SETTING S2 ON A.ID = S2.ID
INNER JOIN SETTING S3 ON A.ID = S3.ID
INNER JOIN SETTING S4 ON A.ID = S4.ID
INNER JOIN SETTING S5 ON A.ID = S5.ID
INNER JOIN SETTING S6 ON A.ID = S6.ID
INNER JOIN SETTING S7 ON A.ID = S7.ID
WHERE S1.NAME="setting1" AND S1.VALUE="a"
AND S2.NAME="setting2" AND S2.VALUE="b"
AND S3.NAME="setting3" AND S3.VALUE="c"
AND S4.NAME="setting4" AND S4.VALUE="d"
AND S5.NAME="setting5" AND S5.VALUE="e"
AND S6.NAME="setting6" AND S6.VALUE="f"
AND S7.NAME="setting7" AND S7.VALUE="g"


This is not very performant when you get into large numbers of records.

Can anyone offer any advice?

cheers,

David