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: 4): A correlated subquery is a subquery that contains a reference (column: 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 Correlated Subqueries (query line: 18): A correlated subquery is a subquery that contains a reference (column: 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 Subqueries (query line: 41): 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'.
- Replace In Subquery With Correlated Exists (modified query below): In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.
Optimal indexes for this query:
ALTER TABLE `ATTRIBUTE_VALUES` ADD INDEX `attribute_values_idx_attribute_code_id` (`ATTRIBUTE_CODE`,`ID`);
ALTER TABLE `PRODUCTS` ADD INDEX `products_idx_ean_is_draft` (`EAN`,`IS_DRAFT`);
The optimized query:
ATTRIBUTE_CODE AS ATTRIB_NAME,
ATTRIBUTE_VALUE AS APP_STATUS
PRODUCTS AS p
p.IS_DRAFT) AS a
NULL) AS b
ON pl.PRODUCT_ID = b.ID
NULL) AS d
PRODUCT_ATTRIBUTE_VALUES AS PRODAV
ON d.ID = PRODAV.PRODUCT_ID
AND EXISTS (
ATTRIBUTE_VALUES.ATTRIBUTE_CODE = 'APPROVED_ATTRIBUTES'
PRODAV.ATTRIBUTE_VALUE_ID = ATTRIBUTE_VALUES.ID
ATTRIBUTE_VALUES AS ATVALS
ON PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID
AND ATVALS.ATTRIBUTE_CODE = 'APPROVED_ATTRIBUTES'