performance - Different Ways of Date Comparision in MySQL -

in mysql there multiple ways, compare dates (date , datetime fields). i'm asking me whether there advantages or disadvantages (performance, query parser, human-readability, ...) on different options).

here variants, know (i think there more...):

select my_date = 20140101 select my_date = "2014-01-01" select date(my_datetime) = 20140101 select year(my_datetime) = 2014 , month(my_datetime) = 1 , day(my_datetime) = 1 select my_datetime between "2014-01-01" , "2014-01-01 23:59:59" 

are there more possibilities?

i think, comparision using date-functions (date, year, month, day) ineffective query optimizer can not use index.

from pure technical point of view

yes, of variants make use of function evaluation slower other solutions. (although might sped optimizer, mysql not count on that.)

i not expect performance difference between first , second variant. 1 distinguish between datetime , timestamp type of column.

• timestamp stored in seconds since hour 0 of unix timestamps (1970...), both values need transformed before database can searched them.

• the datetime values stored integer in format first variant, might faster second variant, since not constructing dynamic values there, string transformed once @ beginning of execution overhead negligible.

you can read internal structures in mysql manual.

the semantics

actually statements not comparable.

assuming type of column datetime, 2 queries

select my_date = 20140101 select my_date = "2014-01-01" 

would find columns 2014-01-01 00:00:00, not 2014-01-01 23:59:59.

the other 3 queries find both rows.

from last 3 queries, 2 make use of functions , therefore not advisable. since using between datetime column types not semantics point of view (especially since introduction of fractional seconds in mysql 5.6.4) choose query:

select * test my_datetime >= "2014-01-01" , my_datetime < "2014-01-02"