Re: Double Index Performance

[Björn Steinbrink]

Sebastien Caisse wrote:

> c) or better yet, CREATE a non-UNIQUE INDEX for
> (postDate DESC, PersonId DESC).

He already has a non-unique index for (PersonId, postDate DESC), which should be better, as sorting has to happen on the last index part to get the index used.

With his index (PersonId, postDate DESC):
----
mysql> EXPLAIN SELECT PostDate FROM Person_Test where PersonId=1 ORDER BY PostDate DESC LIMIT 1;
+----+-------------+-------------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | Person_Test | ref | PersonId | PersonId | 4 | const | 2 | Using where; Using index |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+--------------------------+


With your index (postDate DESC, PersonId DESC):
----
mysql> EXPLAIN SELECT PostDate FROM Person_Test where PersonId=1 ORDER BY PostDate DESC LIMIT 1;
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | Person_Test | index | NULL | PersonId2 | 13 | NULL | 3 | Using where; Using index |
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+--------------------------+