Help optimize this SQL

[elmonty]

The following query is pretty simple. It selects some columns from a messages table. The first time this query is run, it takes 11 seconds. Subsequent runs take about a second (I expect some caching is involved). I am trying to determine why the first time takes so long.

 

SELECT DISTINCT ID,List,`From`,Subject, UNIX_TIMESTAMP(MsgDate) AS FmtDate 
FROM messages
WHERE List='general' 
ORDER BY MsgDate 
LIMIT 17451,20;

 

MySQL version: 4.0.26-log

 

Here's the table:

 

messages  CREATE TABLE `messages` (                                                                                                                                                                      `ID` int(10) unsigned NOT NULL auto_increment,
  `List` varchar(10) NOT NULL default '',
  `MessageId` varchar(128) NOT NULL default '',
  `From` varchar(128) NOT NULL default '',
  `Subject` varchar(128) NOT NULL default '',
  `MsgDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `TextBody` longtext NOT NULL,
  `HtmlBody` longtext NOT NULL,
  `Headers` text NOT NULL,
  `UserID` int(10) unsigned default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `List` (`List`,`MsgDate`,`MessageId`),
  KEY `From` (`From`), 
  KEY `UserID` (`UserID`,`List`,`MsgDate`), 
  KEY `MsgDate` (`MsgDate`),
  KEY `ListOnly` (`List`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC

 

Here's the explain:

table   type    possible_keys  key       key_len  ref       rows  Extra                                       
------  ------  -------------  --------  -------  ------  ------  --------------------------------------------
m       ref     List,ListOnly  ListOnly  10       const    17516  Using where; Using temporary; Using filesort

 

Why is it using a filesort when I have indexes on all the relevant columns?  I added the ListOnly index just to see if it would help. I had originally thought that the List index would handle both the list selection and the sorting on MsgDate, but it didn't. Now that I added the ListOnly index, that's the one it uses, but it still does a filesort on MsgDate, which is what I suspect is taking so long.

 

How can I get it to use an index for sorting?

 

(BTW, this CAPTCHA is unreadable).

 

[elmonty]

Oops, it looks like part of the table definition was accidentally deleted. Here it is again:

 

 CREATE TABLE `messages` (
`ID` int(10) unsigned NOT NULL auto_increment,
`List` varchar(10) NOT NULL default '',
`MessageId` varchar(128) NOT NULL default '',
`From` varchar(128) NOT NULL default '',
`Subject` varchar(128) NOT NULL default '',
`MsgDate` datetime NOT NULL default '0000-00-00 00:00:00',
`TextBody` longtext NOT NULL,
`HtmlBody` longtext NOT NULL,
`Headers` text NOT NULL,
`UserID` int(10) unsigned default NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `List` (`List`,`MsgDate`,`MessageId`),
KEY `From` (`From`),
KEY `UserID` (`UserID`,`List`,`MsgDate`),
KEY `MsgDate` (`MsgDate`),
KEY `ListOnly` (`List`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC

 

[fenway]

Well, that version of mysql can only use one index per query, so having "lots of indexes" won't help.

 

Besides, no matter what you do, skipping ~17000 rows just to show 20 is crazy slow -- ALWAYS.