CODEWITHSUNDEEP
mysqlselect
John
John

Reputation: 261

mysql select query slow despite indexed

Alright, here's the deal:

I have a nice little 4Gb table called Mails on which I do the following two queries:

SELECT * FROM Mails WHERE sent = 1 ORDER BY date ASC LIMIT 600;  // 200ms
SELECT * FROM Mails WHERE sent = 0 ORDER BY date ASC LIMIT 600;  // >9000ms

The relation between sent types is following:

0    192070
1   1112341
2   11992
3   5369

The create statement is this:

CREATE TABLE `Mails` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idMail` varchar(100) COLLATE utf8_bin NOT NULL,
  `type` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `idSender` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `senderfName` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `senderlName` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `senderMail` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `receiverMail` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `reference` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `mailContent` text COLLATE utf8_bin,
  `mailSubject` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `sent` int(1) unsigned DEFAULT '0',
  `opened` int(1) unsigned DEFAULT '0',
  `clicked` int(1) unsigned DEFAULT '0',
  `completed` int(1) unsigned DEFAULT '0',
  `abstract` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idMail` (`idMail`),
  KEY `fk_type` (`type`),
  KEY `fk_idSender` (`idSender`),
  KEY `fk_senderMail` (`senderMail`),
  KEY `fk_receiverMail` (`receiverMail`),
  KEY `fk_sent` (`sent`),
  KEY `fk_reference` (`reference`),
  KEY `fk_date` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=1321784 DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$

Why the heck is the "heavier" query faster or in fact at all loading? Clues to self: It is all related to the order-by clause, because without the date ordering it's lightning fast for both. Bad thing, I need that date ordering badly. I cannot order by the id because mails can be generated into the future and I need the ones that have passed NOW() and have not been sent.

[EDIT 2011-04-14]

The correct answer to the slowdown by AJ can found below. Our solution to this problem was to create a joined index 

KEY `sent` (`sent`,`date`)

Solved absolutely everything.

Upvotes: 1

Views: 1143

Answers (1)

AJ.
AJ.

Reputation: 28184

Use EXPLAIN to determine how MySQL is buffering results for sorting:

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

If you don't have enough sort buffer, it will use temp space on disk, which is slower. See also Tuning Server Parameters, and myisam_sort_buffer_size:

http://dev.mysql.com/doc/refman/5.1/en/server-parameters.html

Upvotes: 2

Related Questions