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 LIKE Searches With Leading Wildcard (query line: 47): The database will not use an index when using like searches with a leading wildcard (e.g. '%Avenue%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
- Avoid Subqueries (query line: 18): 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.
- Avoid Subqueries (query line: 33): 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'.
- Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `Y`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
- Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.
Optimal indexes for this query:
ALTER TABLE `customers` ADD INDEX `customers_idx_name` (`name`);
ALTER TABLE `customers_addresses` ADD INDEX `customers_addresse_idx_id_customer` (`id_customer`);
ALTER TABLE `customers_contacts` ADD INDEX `customers_contacts_idx_id_customer` (`id_customer`);
The optimized query:
X.contact AS contact,
customers.id_customer AS customers_id_customer,
customers.name AS customers_name
customers.name DESC LIMIT 20) AS customers
customers_contacts.value) SEPARATOR ', ') AS contact,
) AS X
ON X.id_customer = customers.customers_id_customer
GROUP_CONCAT(street SEPARATOR '
') AS street,
GROUP_CONCAT(zipcode SEPARATOR '
') AS zipcode,
GROUP_CONCAT(city SEPARATOR '
') AS city,
) AS Y
ON Y.id_customer = customers.customers_id_customer
Y.street LIKE '%Avenue%' LIMIT 20