[Solved] Should I include Order \"by column\" to index?
EverSQL Database Performance Knowledge Base
Should I include Order \"by column\" to index?
I have a query
select * from cp where customer_Id = ? order by date_created desc limit 1
I like to optimize execution of this query bu creating index for customer_Id column.
Question: should my index contain date_created column also ?
How to optimize this SQL query?
The following recommendations will help you in your SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
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.
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.
Optimal indexes for this query:
ALTER TABLE `cp` ADD INDEX `cp_idx_customer_id_date_created` (`customer_Id`,`date_created`);
ALTER TABLE `cp` ADD INDEX `cp_idx_date_created` (`date_created`);
The optimized query:
cp.customer_Id = ?
cp.date_created DESC LIMIT 1