| Bug #15039 | CONVERT_TZ inefficient for large result sets | ||
|---|---|---|---|
| Submitted: | 18 Nov 2005 0:55 | Modified: | 13 Dec 2005 17:39 |
| Reporter: | Thomas Schwanhaeuser | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | All (figures from 4.1.14) | OS: | Any (All) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[13 Dec 2005 17:39]
MySQL Verification Team
I was unable to repeat this issue on latest 4.1. source:
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.17-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT BENCHMARK(10000000,CONVERT_TZ('2004-01-01
'> 12:00:00','UTC','CET'));
+-------------------------------------------------------------------+
| BENCHMARK(10000000,CONVERT_TZ('2004-01-01
12:00:00','UTC','CET')) |
+-------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------+
1 row in set (0.54 sec)
[25 Apr 2015 14:28]
Devon Humes
This is still an issue in MySQL 5. The benchmark is only really fast if the mysql time zone tables are not populated.
Description: Think about having a table which has a timestamp column in UTC. You want to display all times in your local time zone, etc. CET. SELECT *,SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','CET') as localtime WHERE ... When doing time zone conversions for a large number of rows, MySQL seems to unnecessarily determine the time difference for each entry even if it's constant. How to repeat: mysql> SELECT BENCHMARK(10000000,CONVERT_TZ('2004-01-01 12:00:00','UTC','CET')); +-------------------------------------------------------------------+ | BENCHMARK(10000000,CONVERT_TZ('2004-01-01 12:00:00','UTC','CET')) | +-------------------------------------------------------------------+ | 0 | +-------------------------------------------------------------------+ 1 row in set (15.49 sec) But if we would determine ONCE that the time difference between UTC and CET is at the time of the query as to be +1hr and than do mysql> SELECT BENCHMARK(10000000,DATE_ADD('2004-01-01 12:00:00',INTERVAL 1 HOUR)); +---------------------------------------------------------------------+ | BENCHMARK(10000000,DATE_ADD('2004-01-01 12:00:00',INTERVAL 1 HOUR)) | +---------------------------------------------------------------------+ | 0 | +---------------------------------------------------------------------+ 1 row in set (9.82 sec) Suggested fix: - CONVERT_TZ should be changed so that it determines the time difference only once. I recognize that this would cause trouble when you want to do something like CONVERT_TZ(columnA,columnB,columnC). However, in the conjunction with views this could be a real benefit. E.g. you create only one column with a timestamp in UTC and than have localized views with a CONVERT_TZ in it. The effect of the above enhancement would sum up in the long run...