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:
- 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'.
- Use Numeric Column Types For Numeric Values (query line: 21): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
ALTER TABLE `account` ADD INDEX `account_idx_service_id_status_id` (`service_id`,`status`,`id`);
ALTER TABLE `account_data` ADD INDEX `account_data_idx_status_property_value` (`status`,`property_id`,`value`);
ALTER TABLE `property` ADD INDEX `property_idx_create_ana_status_id` (`create_analytics`,`status`,`id`);
The optimized query:
ON ad.property_id = p.id
p.status = 1
ON ad.account_id = ac.id
ac.status = 1
p.create_analytics = '1'
AND ac.service_id = ?
ad.status = 1