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 Selecting Unnecessary Columns (query line: 2): 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: 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.
- Avoid Subqueries (query line: 17): 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 `chauntry` ADD INDEX `chauntry_idx_mailing_indicator` (`mailing_indicator`);
ALTER TABLE `chauntry` ADD INDEX `chauntry_idx_added` (`added`);
ALTER TABLE `chauntry` ADD INDEX `chauntry_idx_email` (`email`);
The optimized query:
'chauntry' AS source,
chauntry.post_code AS postcode
chauntry.mailing_indicator = 1) AS x
Avg(chauntry.amount_paid) AS avg_paid,
Count(*) AS no_times_booked,
'%M %Y'))) AS unique_months
chauntry.added >= Now() - INTERVAL 1 year
) AS y
ON x.email = y.email