MySQL query performance question

Question

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?

Vérace · Accepted Answer · 2023-04-25T08:41:04.653

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the EAV (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.

A great (and hilarious) visual aide to understanding the perils of the EAV model is the image here:

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)
);

Populate:

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

And then run this simple query:

mysql> 
mysql> SELECT *  FROM MyStuff WHERE selector = 1234597951;

Result:

+------------+----------+-----------+-----------+----------+
| selector   | username | password  | firstname | lastname |
+------------+----------+-----------+-----------+----------+
| 1234597951 | person   | PPassword | John      | Smith    |
+------------+----------+-----------+-----------+----------+
1 row in set (0.00 sec)

Soooo much easier!

score 0 · Answer 2 · answered Jul 18 '14 at 19:48

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)

MySQL query performance question

2 Answers2

Linked