Description:
The UNIX_TIMESTAMP function is not treated as a constant, resulting in slow performance:
eg.
# This performs significantly slower (by a factor ~2-3x)
SELECT COUNT(*) FROM t1 WHERE intField > UNIX_TIMESTAMP('2009-03-10 00:00:00');
# Than this form of query...
SELECT COUNT(*) FROM t1 WHERE intField > 1236639600;
oprofiling shows the following for using UNIX_TIMESTAMP:
CPU: Core 2, speed 2000.08 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples % image name symbol name
9302 12.5113 /users/lmulcahy/5.1.39/bin/mysqld str_to_datetime
7386 9.9342 /lib64/libc-2.5.so memcpy
6317 8.4964 /lib64/libc-2.5.so __tzfile_compute
4782 6.4318 /lib64/libc-2.5.so __offtime
3165 4.2570 /lib64/libc-2.5.so __tz_convert
3053 4.1063 /users/lmulcahy/5.1.39/bin/mysqld mi_rnext
2800 3.7660 /users/lmulcahy/5.1.39/bin/mysqld _mi_search_next
oprofile shows the following for using constant INT value:
CPU: Core 2, speed 2000.08 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples % symbol name
5435 12.8093 mi_rnext
4916 11.5861 _mi_search_next
2542 5.9910 _mi_read_key_record
2358 5.5574 handler::read_range_next()
2345 5.5267 _mi_get_static_key
2299 5.4183 _ZL20evaluate_join_recordP4JOINP13st_join_tablei
2055 4.8433 handler::read_multi_range_next(st_key_multi_range**)
2013 4.7443 QUICK_RANGE_SELECT::get_next()
How to repeat:
Create a simple MyISAM table with an indexed int field.
INSERT many rows with graduating int values corrosponding to UNIX Timestamps.
Benchmark SELECT times using UNIX_TIMESTAMP(datetime) and the equivalent constant integer result and compare.
Suggested fix:
Treat UNIX_TIMESTAMP() result as a constant.
Description: The UNIX_TIMESTAMP function is not treated as a constant, resulting in slow performance: eg. # This performs significantly slower (by a factor ~2-3x) SELECT COUNT(*) FROM t1 WHERE intField > UNIX_TIMESTAMP('2009-03-10 00:00:00'); # Than this form of query... SELECT COUNT(*) FROM t1 WHERE intField > 1236639600; oprofiling shows the following for using UNIX_TIMESTAMP: CPU: Core 2, speed 2000.08 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000 samples % image name symbol name 9302 12.5113 /users/lmulcahy/5.1.39/bin/mysqld str_to_datetime 7386 9.9342 /lib64/libc-2.5.so memcpy 6317 8.4964 /lib64/libc-2.5.so __tzfile_compute 4782 6.4318 /lib64/libc-2.5.so __offtime 3165 4.2570 /lib64/libc-2.5.so __tz_convert 3053 4.1063 /users/lmulcahy/5.1.39/bin/mysqld mi_rnext 2800 3.7660 /users/lmulcahy/5.1.39/bin/mysqld _mi_search_next oprofile shows the following for using constant INT value: CPU: Core 2, speed 2000.08 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000 samples % symbol name 5435 12.8093 mi_rnext 4916 11.5861 _mi_search_next 2542 5.9910 _mi_read_key_record 2358 5.5574 handler::read_range_next() 2345 5.5267 _mi_get_static_key 2299 5.4183 _ZL20evaluate_join_recordP4JOINP13st_join_tablei 2055 4.8433 handler::read_multi_range_next(st_key_multi_range**) 2013 4.7443 QUICK_RANGE_SELECT::get_next() How to repeat: Create a simple MyISAM table with an indexed int field. INSERT many rows with graduating int values corrosponding to UNIX Timestamps. Benchmark SELECT times using UNIX_TIMESTAMP(datetime) and the equivalent constant integer result and compare. Suggested fix: Treat UNIX_TIMESTAMP() result as a constant.