MySQL Forums
Forum List  »  Optimizer & Parser

Re: Double Index Performance
Posted by: Björn Steinbrink
Date: January 16, 2006 08:46AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
7845
January 12, 2006 02:21PM
3027
January 13, 2006 05:18PM
2622
January 16, 2006 06:24AM
Re: Double Index Performance
2686
January 16, 2006 08:46AM
2337
January 17, 2006 06:45AM
2530
February 01, 2006 01:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.