[Solved] How can I make this query run faster in SQL?
EverSQL Database Performance Knowledge Base
How can I make this query run faster in SQL?
How can I make this query run faster:
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
WHERE count > 0
It runs about 23 seconds for 10000 records.
How to optimize this SQL query?
The following recommendations will help you in your SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
Avoid Correlated Subqueries In Select Clause (modified query below): The aggregation function located in a subquery inside the SELECT clause, is executed once for every matched row. Extracting this subquery to a temporary table will improve performance significantly.
Avoid Subqueries (query line: 4): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `es_temp1` ADD INDEX `es_temp1_idx_year` (`year`);
ALTER TABLE `laws` ADD INDEX `laws_idx_year` (`year`);
The optimized query:
laws AS l
ON es_temp1.year = l.year
l.year DESC) s
count > 0