title: what's the best way to design dynamic key-value pairs in mysql?
body: <p>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.</p>
  
  <p><strong>1: simple:</strong></p>
  
  <pre><code>table records: id, recordname, valname
  
  select recordname from records where valname = 'myvalue'
  </code></pre>
  
  <p><strong>2: a little more complex:</strong></p>
  
  <pre><code>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'
  </code></pre>
  
  <p><strong>3: most complex:</strong></p>
  
  <pre><code>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'
  </code></pre>