MySQL 5.5 – Query Performance Optimization

mysql-5.5performancequery-performance

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?

Best Answer

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>
Related Question