MySQL 1 millon row query speed

Question

Hi,

I'm having trouble getting a decent query time out of a large MySQL table, currently its taking over 20 seconds. The problem lies in the GROUP BY as MySQL needs to run a filesort but I don't see how I can get around this

QUERY:

SELECT play_date, COUNT(DISTINCT(email)) AS count 
FROM log 
WHERE type = 'play'
AND play_date BETWEEN '2009-02-23' 
AND '2009-02-24'
GROUP BY play_date 
ORDER BY play_date desc

EXPLAIN:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE          log  ALL  type,type_2  NULL  NULL  NULL  530892  Using where; Using filesort

TABLE STRUCTURE

CREATE TABLE IF NOT EXISTS `log` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `type` enum('played','reg','friend') NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `play_date` date NOT NULL,
  `email_refer` varchar(255) NOT NULL,
  `remote_addr` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `email` (`email`),
  KEY `type` (`type`),
  KEY `email_refer` (`email_refer`),
  KEY `type_2` (`type`,`timestamp`,`play_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=707859 ;

If anyone knows how I could improve the speed I would be very greatful

Tom

EDIT

I've added the new index with just play_date and type but MySQL refuses to use it

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE          log  ALL  play_date  NULL  NULL  NULL  801647  Using where; Using filesort

This index was created using ALTER TABLE log ADD INDEX (type, play_date);

Answer 1

You need to create index on fields type AND play_date.

Like this:

ALTER TABLE `log` ADD INDEX (`type`, `play_date`);

Or, alternately, you can rearrange your last key like this:

KEY `type_2` (`type`,`play_date`,`timestamp`)

so MySQL can use its left part as a key.

Answer 2

You should add an index on the fields that you base your search on.

In your case it play_date and type

Answer 3

Does there need to be an index on play_date, or move the position in the composite index to second place?

Answer 4

You're not taking advantage of the key named type_2. It is a composite key for type, timestamp and play_date, but you're filtering by type and play_date, ignoring timestamp. Because of this, the engine can't make use of that key.

You should create an index on the fields type and play_date, or remove timestamp from the key type_2.

Or you could try to incorporate timestamp into your current query as a filter. But judging from your current query I don't think that is logical.

Answer 5

The fastest options would be this

ALTER TABLE `log` ADD INDEX (`type`, `play_date`, 'email');

It would turn this index into a "covering index", which would mean that the query would only access the index stored in memory and not even goto the hard disk.