Assume the query below take a long time to run. To improve the running time of this SQL query, which of the options below are the best way to do so?

SELECT Name
FROM People
WHERE Birth_Date > CAST('2000-01-01' AS DATE) ;

People is the name of the table. Now there are 4 options:

1. Replace the expression Birth_Date with CAST (Birth_Date AS DATE) to avoid type conversions.
2. CREATE INDEX IDX ON People (Birth_Date, Name)
3. CREATE INDEX IDX ON People (Name, Birth_Date)
4. Create a view that consists of this query with the actual date of birth as an input parameter.

Which of the option is the best and why?

CodePudding user response：

Option 2.

Option 1 performs an unnecessary type conversion as we may assume that Birth_Date is already a DATE (or compatible type), why else would '2000-01-01' be cast to DATE? It would not help to speed up the query, only to slow it down more, because of the extra casting.

Option 2 creates an index whose first member is Birth_Date, so the engine can use an efficient search in the index to find the record with the least Birth_Date that meets the WHERE condition and then output all records from there on, following the index order.

Option 3 creates an index that is not useful, since Birth_Date is not its first member, so it cannot be used to efficiently find the record with the least Birth_Date, nor to traverse records in order of Birth_Date.

Option 4 is not possible, as views don't take parameters. Even if a certain database engine would know about this concept, it would only affect the right side of the WHERE condition, not the algorithm that the database engine can use for retrieving the matching records.

CodePudding user response：

Basically, you only need create index on People(Birth_Date). Because, only this column is involved the selectivity as the condition.

1. Please don't CAST (Birth_Date AS ), casting on the column type will cause the related index no effect unless you create function index. No need to do it.
2. For your 2 and 3, there is no need to create combound index. Column name is not in the condition list. No help for the efficiency.
3. this is a simple query, no need to create a view for that.