How can I make this query run faster:
SELECT *
FROM ( SELECT DISTINCT l.year,
(select count(laws.id) from laws as laws WHERE laws.year = l.year ) as count
FROM laws as l
ORDER BY l.year DESC
) s
WHERE count > 0
It runs about 23 seconds for 10000 records.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `es_temp1` ADD INDEX `es_temp1_idx_year` (`year`);
ALTER TABLE `laws` ADD INDEX `laws_idx_year` (`year`);
SELECT
*
FROM
(SELECT
DISTINCT l.year,
es_temp1.count
FROM
laws AS l
LEFT JOIN
es_temp1
ON es_temp1.year = l.year
ORDER BY
l.year DESC) s
WHERE
count > 0