I have a MySQL database storing persons and their associated characteristics.
person table:
+----+--------+
| id | name |
+----+--------+
| 1 | Bella |
| 2 | Jacob |
| 3 | Edward |
| 4 | Renée |
| 5 | Alice |
+----+--------+
feature table:
+----+----------+
| id | name |
+----+----------+
| 1 | Bravery |
| 2 | Shyness |
| 3 | Kindness |
| 4 | Madness |
+----+----------+
person_features table:
+-----------+------------+-------+
| person_id | feature_id | value |
+-----------+------------+-------+
| 1 | 1 | 50 |
| 1 | 2 | 84 |
| 1 | 4 | 10 |
| 2 | 1 | 8 |
| 2 | 2 | 78 |
| 2 | 4 | 41 |
| 3 | 3 | 27 |
| 4 | 1 | 36 |
| 4 | 3 | 64 |
| 5 | 2 | 78 |
| 5 | 3 | 2 |
+-----------+------------+-------+
Let's say I want the list of all the persons ordered by descending shyness, kindness and bravery (with the value of these features for each person):
+--------+---------+----------+---------+
| person | Shyness | Kindness | Bravery |
+--------+---------+----------+---------+
| Bella | 84 | NULL | 50 |
| Alice | 78 | 2 | NULL |
| Jacob | 78 | NULL | 8 |
| Renée | NULL | 64 | 36 |
| Edward | NULL | 27 | NULL |
+--------+---------+----------+---------+
I currently use this dynamically generated query:
SELECT person.name, pf2.value, pf3.value, pf1.value
FROM person
LEFT JOIN person_features pf2 ON person.id = pf2.person_id AND pf2.feature_id = 2
LEFT JOIN person_features pf3 ON person.id = pf3.person_id AND pf3.feature_id = 3
LEFT JOIN person_features pf1 ON person.id = pf1.person_id AND pf1.feature_id = 1
ORDER BY pf2.value DESC, pf3.value DESC, pf1.value DESC, person.name;
But it's a little slow with many features, because I must add a left join for each one. So, is there a way to use a more universal static query instead of a dynamic one? Even if it means a post-processing treatment in my PHP script to regroup data.
CREATE statements:
CREATE TABLE `feature` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `person_features` (
`person_id` int(11) NOT NULL,
`feature_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`person_id`,`feature_id`),
KEY `feature_id` (`feature_id`),
CONSTRAINT `person_features_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
CONSTRAINT `person_features_ibfk_2` FOREIGN KEY (`feature_id`) REFERENCES `feature` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Result of EXPLAIN:
+----+-------------+--------+--------+--------------------+---------+---------+----------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+--------------------+---------+---------+----------------------+------+---------------------------------+
| 1 | SIMPLE | person | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
| 1 | SIMPLE | pf2 | eq_ref | PRIMARY,feature_id | PRIMARY | 8 | test.person.id,const | 1 | |
| 1 | SIMPLE | pf3 | eq_ref | PRIMARY,feature_id | PRIMARY | 8 | test.person.id,const | 1 | |
| 1 | SIMPLE | pf1 | eq_ref | PRIMARY,feature_id | PRIMARY | 8 | test.person.id,const | 1 | |
+----+-------------+--------+--------+--------------------+---------+---------+----------------------+------+---------------------------------+
I'm not sure if this would be faster, but you can try using conditional aggregation:
select p.name,
max(case when pf.feature_id = 2 then value end) as shyness,
max(case when pf.feature_id = 3 then value end) as kindness,
max(case when pf.feature_id = 1 then value end) as bravery
from person p join
person_features pf
on p.person_id = pf.person_id
group by p.name
order by shyness desc, kindess desc, bravery desc;
Also, an index on person_features(person_id, feature_id, value) would speed up your query (as well as this one).