Sql

Which of the following queries would you use?

SELECT count(*)                             SELECT count(*)
FROM shawarma_purchases                     FROM shawarma_purchases
WHERE                               vs.     WHERE
  YEAR(purchased_at) == '2017'              purchased_at >= '2017-01-01' AND
                                            purchased_at <= '2017-31-12'

Difficulty: unrated

Source: bregman-arie/devops-exercises by Arie Bregman

Answer

SELECT count(*)
FROM shawarma_purchases
WHERE
  purchased_at >= '2017-01-01' AND
  purchased_at <= '2017-31-12'

When you use a function (YEAR(purchased_at)) it has to scan the whole database as opposed to using indexes and basically the column as it is, in its natural state.