MySQL 1 millionth query speed

Question

I'm having trouble getting a decent query time from a large MySQL table, it currently takes more than 20 seconds. The problem is GROUP BY, since MySQL has to run the file port, 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 someone knows how I could improve speed, I would be very grateful

Tom

EDIT

I added a new index using only play_date and enter, 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 an index in the type AND play_date .

Like this:

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

Or, alternately, you can change your last key as follows:

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

therefore, MySQL can use the left side as a key.

Answer 2

You must add the index to the fields on which you based the search.

In your case, this is play_date and enter

Answer 3

You are not using a key named type_2 . This is a composite key for type , timestamp and play_date , but you filter type and play_date , ignoring timestamp . Because of this, the engine cannot use this key.

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

Or you can try to include timestamp in your current request as a filter. But judging by your current request, I do not think this is logical.

Answer 4

Should there be a pointer to play_date or move the position in the composite index to second place?

Answer 5

The fastest options:

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

It will turn this index into a “coverage index”, which means that the request will have access only to the index stored in memory, and not even to the hard drive.

Answer 6

The DESC parameter causes MySQL not to use the index for ORDER BY. You can leave it ASC and repeat the result set in the opposite direction on the client side (?).