Double Index Performance
Hey,
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)
) ENGINE=MyISAM
Put Some dummy data inside:
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','Hello
Mysql',now());
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','How are
you',now());
INSERT INTO Person_Test (PersonId, PostBody, PostDate) VALUES ('1','I\'m
Good',now());
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?
THanks
Jawad