EverSQL Database Performance Knowledge Base

The optimization process and recommendations:

1. 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.
2. 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.
3. 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:

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