Select query with subselects slows down when constrained

Question

This query runs pretty speedily:

select mainsearch.product,
(select count(id) from search_log 
    where search_log.hits > 0 
    and search_log.`time` between '2015-09-01 00:00:00' and '2015-09-01 23:59:59' 
    and mainsearch.product = search_log.product
) as success
from search_log as mainsearch
group by mainsearch.product;

This query takes forever:

select mainsearch.product,
(select count(id) from search_log 
    where search_log.hits > 0 
    and search_log.`time` between '2015-09-01 00:00:00' and '2015-09-01 23:59:59' 
    and mainsearch.product = search_log.product
) as success
from search_log as mainsearch
where mainsearch.`time` between '2015-09-01 00:00:00' and '2015-09-01 23:59:59' 
group by mainsearch.product;

Here's the table specification:

CREATE TABLE `search_log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `query` text NOT NULL,
    `hits` mediumint(9) NOT NULL DEFAULT '0',
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `product` varchar(16) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `time_index` (`time`),
    KEY `searchlog_product` (`product`)
);

I know a little bit about optimisation, and the theory behind subselects - but can't figure why this would have such an impact, nor how to resolve it.

Answer 1

You can avoid using correlated subquery at all. Use CASE WHEN:

select 
   mainsearch.product,
   COUNT(CASE WHEN hits > 0 THEN 1 ELSE NULL END) AS success
from search_log as mainsearch
where mainsearch.`time` between '2015-09-01 00:00:00' and '2015-09-01 23:59:59' 
group by mainsearch.product;

You can also tweak it a little skipping ELSE

COUNT(CASE WHEN hits > 0 THEN 1 END) AS success

or even go one step futher and use (works in MySQL):

SUM(hits > 0) AS success