what's the best way to design dynamic key-value pairs in mysql?

Question

Hi, I need to attach unlimited attributes to a record in a table, and I've already designed a system using #3 below using mysql. Unfortunately I am finding that searching across a million records is getting slow. Is #2 a better approach, or is there a better way alltogether? Is this a case for using a view? I'd like to have my keys table separate so I know what attributes are being stored for each record.

1: simple:

table records: id, recordname, valname

select recordname from records where valname = 'myvalue'

2: a little more complex:

table records: id recordname
table keyvalues: id recordid keyname valname

    select r.recordname 
    from records r
    right join keyvalues kv on kv.recordid = r.id 
       and kv.keyname='mykey' 
       and kv.valname = 'myvalue'

3: most complex:

table records: id recordname
table keys: id keyname
table values: id recordid keyid valname   

 select r.recordname 
    from records r
    right join keys k on k.keyname='mykey'
    right join values v on v.recordid = r.id 
       and v.keyid = k.id 
       and v.valname = 'myvalue'

Answer 1

I would use inner joins. This will give a smaller result set and the one you want.

Did you try this query?

select r.recordname
from records r
left join values link on link.recordid = r.id and link.valname = 'myvalue'
left join keys k on r.keyid = link.key.id and k.keyname = 'mykey'

However, I think the real way to do it is to have 4 tables

table records: id recordname
table keys: id keyname
table values : id valuename
table joins : id recordid keyid valueid

Then (with the right indexes) you can have a query like this

select r.recordname
from joins j
left join records r on j.recordid = r.id
left join keys k on j.keyid = k.id
left join values v on j.valueid = v.id
where v.valuename = 'myvalue' and k.keyname = 'mykey'

This should be quite fast... all it has to do is find the id in values and keys and then do a scan on j. If you have the right indexes these will be quick.