1

I have a MySQL database where I would like to return results based on multiple rows.

id | key        | value      | selector
---------------------------------------------------------
1  | username   | person     | 123459795181089198123
2  | password   | PPassword! | 123459795181089198123
3  | firstname  | John       | 123459795181089198123
4  | lastname   | Smith      | 123459795181089198123

Basically, I'm trying to avoid having many columns and altering the database tables, by creating the key/value/selector style instead.

Now the problem I'm having is with slowness on a server I use. Right now, my SQL query looks something like this:

SELECT *
FROM users
WHERE selector IN (
    SELECT selector
    FROM users
    WHERE (`key` = 'username' && `value` = 'person') || (`key` = 'firstname' && `value` = 'John')) AND `key` = 'password';

This returns something like:

id | key        | value      | selector
---------------------------------------------------------
2  | password   | PPassword! | 123459795181089198123

It works great, except that I'm running this on 20,000 records which causes the performance to drop drastically.

My question is, is there a better way to do this while using the same type of data structure?

  • The technique you are using is called Entity Attribute Value (EAV). It is famous for getting it's users into performance as well as consistency trouble. I would strongly advise you to reconsider this way of doing things, and instead design a proper schema. – Lennart Jul 18 '14 at 19:46
  • You could also take a look at this - it's most of Karwin's book in slides. – Vérace Jul 18 '14 at 20:38
3

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would IMHO be far better off if you redesigned your table structure.

What you should be doing is something like this - and your performance should be more than acceptable.

CREATE TABLE MyStuff
(
  selector int, 
  username VARCHAR(15), 
  password VARCHAR(15), 
  firstname VARCHAR(15), 
  lastname VARCHAR(15),
  PRIMARY KEY(selector)
);    

mysql> 
mysql> INSERT INTO MyStuff VALUES(1234597951, 'person', 'PPassword', 'John', 'Smith');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT *  FROM MyStuff WHERE selector = 1234597951;
+------------+----------+-----------+-----------+----------+
| selector   | username | password  | firstname | lastname |
+------------+----------+-----------+-----------+----------+
| 1234597951 | person   | PPassword | John      | Smith    |
+------------+----------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql> 
0

Queries with WHERE column IN (SELECT subquery) may not be best optimized in various version of MySQL. The usual solution is to rewrite using joins and of course add appropriate indexes.

The query can be rewritten equivalently as:

SELECT u.*
FROM users AS u
  JOIN ( SELECT selector
         FROM users AS u2
         WHERE u2.key = 'username' AND u2.value = 'person' 
            OR u2.key = 'firstname' AND u2.value = 'John' 
       ) AS s
     ON s.selector = u.selector
WHERE u.key = 'password' ;

or as:

SELECT u.*
FROM users AS u
  JOIN users AS s
    ON s.selector = u.selector
WHERE ( s.key = 'username' AND s.value = 'person' 
     OR s.key = 'firstname' AND s.value = 'John' 
      )
  AND u.key = 'password' ;

You'll also need indexes:

  • an index on (key,value) and
  • an index on either (key, selector) or on (selector, key)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.