Very slow MYSQL query for 2.5 million row table

Question

I'm really struggling to get a query time down, its currently having to query 2.5 million rows and it takes over 20 seconds

here is the query

SELECT play_date AS date, COUNT(DISTINCT(email)) AS count FROM log WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01' AND type = 'play' GROUP BY play_date ORDER BY play_date desc;   `id` int(11) NOT NULL auto_increment,   `instance` varchar(255) NOT NULL,   `email` varchar(255) NOT NULL,   `type` enum('play','claim','friend','email') NOT NULL,   `result` enum('win','win-small','lose','none') 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 `result` (`result`),   KEY `timestamp` (`timestamp`),   KEY `email_refer` (`email_refer`),   KEY `type_2` (`type`,`timestamp`),   KEY `type_4` (`type`,`play_date`),   KEY `type_result` (`type`,`play_date`,`result`)  id  select_type table   type    possible_keys   key key_len ref rows    Extra 1   SIMPLE  log ref type_2,type_4,type_result   type_4  1   const   270404  Using where 

The query is using the type_4 index.

Does anyone know how I could speed this query up?

Thanks Tom

Answer 1

That's relatively good, already. The performance sink is that the query has to compare 270404 varchars for equality for the COUNT(DISTINCT(email)), meaning that 270404 rows have to be read.

You could be able to make the count faster by creating a covering index. This means that the actual rows do not need to be read because all the required information is present in the index itself.

To do this, change the index as follows:

KEY `type_4` (`type`,`play_date`, `email`) 

I would be surprised if that wouldn't speed things up quite a bit.

(Thanks to MarkR for the proper term.)