Very slow MYSQL query for 2.5 million row table

Viewed 11929

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

8 Answers
Related