0

There was an SO question Error matching dates in PDO which I gave an answer on and had some conversation around.

Essentially I said not use wildcards on dates:

SELECT * FROM table where datefiled LIKE '%2018-06%'

And instead use something like (yes a between would work too):

SELECT * FROM table where MONTH(datefield) = '06' and YEAR(datefield) = '2018

This lead to a comment on my answer that stated he was told (data with +90 million rows):

When you apply a function on a column it voids usage of the index for lookups, as it would have to apply the same function to ALL the values in the index and hence would be less effective even. Hence the need to use the trick with dates boundaries.

My understanding of the search algorithms SQL implements is not that great but I get when to use and not to use an index (reading vs writing). The above seems to imply that using the MONTH() and YEAR() function would be less effective then a string LIKE. If the comment was about BETWEEN I would get it but I cannot conceive of a use case where LIKE would win out because of the function call.

If it can what is that use case?

nerdlyist
  • 2,842
  • 2
  • 20
  • 32

1 Answers1

4

LIKE requires converting the date to a string. Although the conversion is implicit, it is still equivalent to a function call and (generally) prevents the use of an index.

Similarly, MONTH() and YEAR() are function calls and they prevent the use of an index.

Instead, just write an inequality:

SELECT t.*
FROM table t
WHERE datefield >= '2018-06-01' AND datefield < '2018-07-01'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So in the end it is not that `LIKE` would outperform `YEAR()` and `MONTH()` but that equity would be faster then both regardless because SQL can use the indexes more effectively? I ran some test with `LIKE` vs `YEAR()` and `MONTH()` the latter seemed to be much more efficient. – nerdlyist Jun 27 '18 at 11:23
  • @nerdlyist . . . I'm surprised that it would be *much* more efficient, but operations on date parts should be faster than `like` pattern matching. I think the query would still be reading all the rows, which is the bigger driver of performance. – Gordon Linoff Jun 27 '18 at 12:18
  • What was odd was that the `LIKE` returned and empty set even with matching data and took seconds as opposed to the parts which returned data in milliseconds. – nerdlyist Jun 27 '18 at 13:47
  • @nerdlyist . . . You haven't specified your database. Perhaps your database is smart enough to use indexes for date parts. – Gordon Linoff Jun 28 '18 at 01:29