Is the following SQL good or bad practice from a performance perspective?

Question

I was hoping if any of you could be of any help, if I have a huge database with million of entries and all I need is information relating to a specific year (say, 2015), if I use the following query, performance wise will this be best practice or is there another way to have a better query,

CREATE INDEX table1_idx ON table1 (date_column);
SELECT text, date_column
FROM table1
WHERE datepart(yyyy, date_column) = 2015;

Any help relating to this will be highly appreciated.

Please check this link http://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices. This may answer your question — Sankar Krishnamoorthy, Apr 30 '16 at 04:50

score 0 · Answer 1 · answered Apr 30 '16 at 05:04

If this is about SQL Server, that is not the way to do it. Using any functions on the fields makes in non-SARGable.

Always use correct search criteria like this:

SELECT text, date_column
FROM table1
WHERE date_column >= '20150101' and date_column < '20160101'

You might also want to consider adding text as included column to prevent key lookups:

CREATE INDEX table1_idx ON table1 (date_column) include (text)

score 0 · Answer 2 · answered Apr 30 '16 at 05:09

MySQL can't use an index range scan operation, due to the function wrapped around the column. The function has to be evaluated for every row in the table.

To get an index range scan operation, do comparisons on the bare column.

For example:

 SELECT t.text
      , t.date_column
   FROM table1 t
  WHERE t.date_column >= '2015-01-01'
    AND t.date_column  < '2015-01-01' + INTERVAL 1 YEAR

Is the following SQL good or bad practice from a performance perspective?

2 Answers2