SQL select with DATE in WHERE, what is faster?

Question

I have a query from table of rows, where is datetime column with only year and month. The day is always 01 and time is 00:00:00

When selecting data with php query, what is faster?

$date = "2020-04";
$query = "SELECT * FROM table WHERE datum LIKE ?",$date ;

or

$date = "2020-04";
$rok = substr($mesic,0,4);
$mesic = substr($mesic,5,2);
$query = "SELECT * FROM table WHERE YEAR(datum) = ? AND MONTH(datum) = ?",$rok,$mesic;

The table contains 100s thousands of rows

Why are you asking us? You've got the data, can't you test it yourself? — HoneyBadger, Apr 21 '20 at 08:20
please read this answer https://stackoverflow.com/questions/6142235/sql-like-vs-performance — Hadi Heydarzade, Apr 21 '20 at 08:22
Does this answer your question? [SQL 'like' vs '=' performance](https://stackoverflow.com/questions/6142235/sql-like-vs-performance) — Haem, Apr 21 '20 at 11:24

GMB · Answer 1 · 2020-04-21T08:26:35.280

I would recommend:

$date = "2020-04";
$sql = "SELECT * FROM table WHERE datum = concat(?, '-01')", $date;

The base idea is not not apply functions (either date functions or pattern matching) on the column being searched, as this prevents the database from taking full advantage of an index on that column (and it involves unnecessary additional computation for each and every row). Instead, you can easily generate the exact value to search for with by concatenating the variable in the query.

In the more typical case where datum had real day and time components, and you want to filter on a given month, you would do:

SELECT *
FROM table
WHERE datum >= concat(?, '-01') AND datum < concat(?, '-01') + interval 1 month

Note that this assumes that you are using MySQL, because the syntax suggests it. Other database have equivalent ways to build dates from strings.

score 0 · Answer 2 · answered Apr 21 '20 at 08:25

We always used to have the rule:

"Avoid functions in the WHERE Clause".

The background is that the database server has to make a table scan to calculate the function result for each row (even if it is only the key table). So he cannot use the keys efficiently!

LIKE is faster! If you use the beginning of the key (as you write), it can also use it efficiently.

score 0 · Answer 3 · answered Apr 21 '20 at 11:47

Neither. In both cases you have a function call on the datum column. With YEAR() and MONTH() it is obvious. With LIKE you are converting to a string. Both impede the optimize and prevent the use of indexes.

The correct structure would be:

where datum >= ? and
      datum < ? + interval 1 month  -- syntax might vary depending on the database

where ? are parameter place-holders for the beginning of the month. I would suggest that you construct this in the application as a date constant or a string constant of the canonical form YYYY-MM-DD.

SQL select with DATE in WHERE, what is faster?

3 Answers3