显示同一行上某个项目的某些特征（位于单独的表中）

Question

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 |                                 |
+----+-------------+--------+--------+--------------------+---------+---------+----------------------+------+---------------------------------+

Answer 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).