MySQL Forums
Forum List  »  Optimizer & Parser

Double Index Performance
Posted by: Jawad Laraqui
Date: January 12, 2006 02:21PM


I have a table of posts for different users and I want to pull out the date of the last post for the users in the most efficient way possible. I have tried many things but I don't know which one actually performs the best. This is for an extremely high concurrency application so I'm really looking for the absolutely best performance: doing a scan in memory of the index is not good enough. I really want to set it up so that MySQL finds the answer in one shot without cursing around indexes.

Take the following example table:

CREATE TABLE `Person_Test` (
`PersonId` int(11) NOT NULL default '0',
`PostBody` text,
`PostDate` datetime default NULL,
KEY `PersonId` (`PersonId`,`PostDate` DESC)

Put Some dummy data inside:
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','Hello
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','How are
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','I\'m

Table looks like this:
mysql> SELECT * FROM Person_Test;
| PersonId | PostBody | PostDate |
| 1 | Hello Mysql | 2006-01-05 15:57:22 |
| 1 | How are you | 2006-01-05 15:57:28 |
| 1 | I'm Good | 2006-01-05 15:57:39 |
3 rows in set (0.00 sec)

I want to get the date of the latest post:

SELECT PostDate FROM Person_Test where PersonId=1 ORDER BY PostDate DESC LIMIT 1

pros: simplest SQL statement to do the job
cons: probably does a scan of the whole index for PersonId to get to the end of his section, then grabs the last element; this is not so great if it's true because the person could have a lot of posts. I don't want it to scan, even though it's in memory because it takes too long on a system where there a lots of users trying to do the same thing.

Does anyone know if there is a better way to implement this using HANDLER?


Options: ReplyQuote

Written By
Double Index Performance
January 12, 2006 02:21PM
January 13, 2006 05:18PM
January 16, 2006 06:24AM
January 16, 2006 08:46AM
January 17, 2006 06:45AM
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.