0

I'm using Mysql 5.6 and I run many queries based on datetime.

When scanning records in a table in a certain datetime range, which one do you think runs faster between two queries below, especially when comparing value is string type? Of course, datetime column is indexed.

A. using BETWEEN

SELECT user_id 
FROM users 
WHERE created_at  BETWEEN '2018-11-01 00:00:00' AND '2018-11-30 23:59:59'

B. using >=, <=

SELECT user_id 
FROM users 
WHERE created_at >= '2018-11-01 00:00:00' AND '2018-11-30 23:59:59' >= created_at
Salman A
  • 262,204
  • 82
  • 430
  • 521
Ryan Kang
  • 61
  • 1
  • 5
  • 3
    Possible duplicate of [What's mysql's "BETWEEN" performance over..?](https://stackoverflow.com/questions/4382892/whats-mysqls-between-performance-over) – Madhur Bhaiya Nov 28 '18 at 07:11
  • I read the replies on the question you mentioned, but the question was the case when setting comparing values as an integer. – Ryan Kang Nov 28 '18 at 07:42
  • It really does not matter whether it is an integer. Any good optimizer should take care of this and handle this the best way possible whether using `between.. and` or `> / <=` – Madhur Bhaiya Nov 28 '18 at 07:45

2 Answers2

1

My guess is that it won't matter, but you can benchmark those with the SHOW PROFILE option of MySQL. The basic usage gives you the duration of different statuses when processing the query, but there are more specific measures for different resources.

set the profiles on for the session:

SET profiling = 1;

Run your first version of the query in a very large dataset and check the profile with:

SHOW PROFILE;

Then, repeat the same steps for the other query.

I'd repeat this process several times and is possible during different days to be sure other processes running on the server don't affect much your results and final comparison.

This is an example of what I've got with a local test I did, but only to show what figures you get from the SHOW PROFILE. The table was small and I only run the queries once each. ;)

  • for BETWEEN filtering

    starting    0.000045
checking permissions    0.000005
Opening tables  0.000011
init    0.000020
System lock 0.000006
optimizing  0.000017
statistics  0.000011
preparing   0.000008
executing   0.000003
Sending data    0.000535
end 0.000004
query end   0.000006
closing tables  0.000006
freeing items   0.000015
cleaning up 0.000009

  • for >= AND <= filtering

    starting    0.000048
checking permissions    0.000004
Opening tables  0.000010
init    0.000025
System lock 0.000005
optimizing  0.000010
statistics  0.000011
preparing   0.000008
executing   0.000003
Sending data    0.000531
end 0.000004
query end   0.000005
closing tables  0.000005
freeing items   0.000016
cleaning up 0.000008
tiomno
  • 2,178
  • 26
  • 31
1

Both queries are identical and there is absolutely no difference. Quote from SQL92 specs:

6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
Salman A
  • 262,204
  • 82
  • 430
  • 521