Very slow MYSQL query for 2.5 million row table
mysql, optimization
Solution
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.)
Problem
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