For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:
The optimization process and recommendations:
- Avoid Correlated Subqueries (query line: 5): A correlated subquery is a subquery that contains a reference (column: updated_at) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
- Avoid Correlated Subqueries (query line: 12): A correlated subquery is a subquery that contains a reference (column: threeable_id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
- Avoid Selecting Unnecessary Columns (query line: 13): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
- Avoid Subqueries (query line: 15): 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.
- Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `threes` ADD INDEX `threes_idx_updated_at` (`updated_at`);
ALTER TABLE `threes` ADD INDEX `threes_idx_deleted_at_threeable_threeable` (`deleted_at`,`threeable_id`,`threeable_type`);
ALTER TABLE `twos` ADD INDEX `twos_idx_id` (`id`);
The optimized query:
MAX(updated_at) AS updated_at
threes.deleted_at IS NULL
th.threeable_id = thmax.threeable_id
AND th.updated_at = thmax.updated_at
AND th.threeable_type = thmax.threeable_type
ON ((th.threeable_id = twos.id
AND th.threeable_type = 'App\\Two')
OR (th.threeable_id = twos.product_id
AND th.threeable_type = 'App\\One'))
ON twos.id = twthmax.twoId
twthmax.threeMaxUA = threes.updated_at
threes.threeable_id = twos.id
AND threes.threeable_type = 'App\\Two'
threes.threeable_id = twos.product_id
AND threes.threeable_type = 'App\\One'