What indices would speed up this slow running query?

Question

Why does this query run very slow on MySQL 5.5.30?:

SELECT message.mid,
       message.sender,
       message.date,
       message.subject,
       message.body,
       message.folder,
       sender.firstname,
       sender.lastname,
       sender.status,
       Group_concat(DISTINCT recipientinfo.rvalue) AS recipientAddresses,
       Group_concat(DISTINCT recipients.lastname)  AS recipientLastNames,
       Group_concat(recipients.status)             AS recipientPositions
FROM   message
       LEFT JOIN employeelist AS sender
              ON message.sender = sender.email_id
       LEFT JOIN recipientinfo
              ON message.mid = recipientinfo.mid
       LEFT JOIN employeelist AS recipients
              ON recipientinfo.rvalue = recipients.email_id
GROUP  BY recipientinfo.mid
ORDER  BY NULL;

Results of EXPLAIN

EDIT: Added CREATE TABLE output:

| message | CREATE TABLE `message` (
  `mid` int(10) NOT NULL DEFAULT '0',
  `sender` varchar(127) NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL,
  `message_id` varchar(127) DEFAULT NULL,
  `subject` text,
  `body` text,
  `folder` varchar(127) NOT NULL DEFAULT '',
  PRIMARY KEY (`mid`),
  KEY `sender` (`sender`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

| employeelist | CREATE TABLE `employeelist` (
  `eid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(31) NOT NULL DEFAULT '',
  `lastName` varchar(31) NOT NULL DEFAULT '',
  `Email_id` varchar(31) NOT NULL DEFAULT '',
  `Email2` varchar(31) DEFAULT NULL,
  `Email3` varchar(31) DEFAULT NULL,
  `EMail4` varchar(31) DEFAULT NULL,
  `folder` varchar(31) NOT NULL DEFAULT '',
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`eid`),
  UNIQUE KEY `Email_id` (`Email_id`),
  KEY `firstName` (`firstName`),
  KEY `lastName` (`lastName`),
  KEY `status` (`status`),
  KEY `Email2` (`Email2`,`Email3`,`EMail4`)
) ENGINE=MyISAM AUTO_INCREMENT=153 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |

| recipientinfo | CREATE TABLE `recipientinfo` (
  `rid` int(10) NOT NULL DEFAULT '0',
  `mid` int(10) unsigned NOT NULL DEFAULT '0',
  `rtype` enum('TO','CC','BCC') DEFAULT NULL,
  `rvalue` varchar(127) DEFAULT NULL,
  `dater` datetime DEFAULT NULL,
  PRIMARY KEY (`rid`),
  KEY `rvalue` (`rvalue`),
  KEY `mid` (`mid`),
  KEY `rtype` (`rtype`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

I have added the index on the recipientinfo table, but I don't know what I should index on the message table to make it use a key. I guess from other information I found that I should use a composite index, but using what columns of which table? Is the "using temporary" avoidable at all? Any hint or help is appreciated :)

For some reason the group_by which is required for the group_concat implies an order_by for MySQL and this results in a filesort. If I order by null, I can get rid of the filesort :) (http://dev.mysql.com/doc/refman/5.1/de/order-by-optimization.html) — Ynv, Feb 24 '13 at 19:08
That's a _lot_ of data. 250k rows * 100 characters per row (I'm _very_ conservative) = 25 MB over the wire. 25 MB / 1MBps = 25 seconds. — John Dvorak, Feb 24 '13 at 19:22
Yeah - it's the Enron mail corpus :) It does not have to be transmitted as it is just used locally. I'm most concerned that there is no index used on the message table. It kind of make sense to me, as all messsages will have to be traversed anyway since I request them all, but there might still be something I could do by joining differntly or creating a pair of indices? — Ynv, Feb 24 '13 at 19:29
Can you post the table structure (the output of `SHOW CREATE TABLE`)? — ypercubeᵀᴹ, Feb 24 '13 at 21:17
Do you need to use left joins? Do you need to see all your data in a single query? — symcbean, Feb 24 '13 at 21:20
I use it to create a search index using Lucene. I figured it would be best to first retrieve all data and then just run the index creation on a single, large result-set. Is this Naive? — Ynv, Feb 24 '13 at 22:46
What is the execution plan of your query - you need this before you can make inteligent decisions on your indexing. — Dale M, Feb 25 '13 at 01:25
Hi Dale, I have posted the results of executing "EXPLAIN ..." on the query. Is this what you mean? — Ynv, Feb 25 '13 at 07:41
What indices would speed up this slow running query? Clustered ones, namely, the ones you get with the innodb engine :) http://stackoverflow.com/questions/4419499/mysql-and-nosql-help-me-to-choose-the-right-one/4421601#4421601 — Jon Black, Feb 25 '13 at 15:49

What indices would speed up this slow running query?

0 Answers0