Reputation: 1651

MySQL 1 millon row query speed

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);

Upvotes: 4

Answers (6)

georgepsarakis

Reputation: 1957

The DESC parameter is causing MySQL not to use the index for the ORDER BY. You can leave it ASC and iterate the resultset in reverse on the client side (?).

Upvotes: 0

Kuroki Kaze

Reputation: 8481

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.

Upvotes: 11

Jonathan

Reputation: 2233

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.

Upvotes: 0

Welbog

Reputation: 60408

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.

Upvotes: 1