| Bug #69801 | performance regression between 5.5 and 5.6 for str_to_date function | ||
|---|---|---|---|
| Submitted: | 20 Jul 2013 8:38 | ||
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S5 (Performance) |
| Version: | 5.6.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | STR_TO_DATE | ||
[20 Jul 2013 8:39]
MySQL Verification Team
profile of str_to_date
Attachment: bug69801_5.6.14_str_to_date_profile.png (image/png, text), 30.75 KiB.
[20 Jul 2013 18:31]
Davi Arnaut
5.5 also uses sprintf in my_date_to_str. Perhaps you mean 5.1 and earlier?
[15 Nov 2019 11:10]
MySQL Verification Team
Some updates....
mysql> select benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')),version();
+----------------------------------------------------------------+-----------+
| benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')) | version() |
+----------------------------------------------------------------+-----------+
| 0 | 8.0.17 |
+----------------------------------------------------------------+-----------+
1 row in set (29.21 sec)
mysql> select benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')),version();
+----------------------------------------------------------------+-----------+
| benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')) | version() |
+----------------------------------------------------------------+-----------+
| 0 | 8.0.18 |
+----------------------------------------------------------------+-----------+
1 row in set (16.25 sec)
mysql> select benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')),version();
+----------------------------------------------------------------+-----------+
| benchmark(100000000,str_to_date('2013 November 1','%Y %M %d')) | version() |
+----------------------------------------------------------------+-----------+
| 0 | 5.7.28 |
+----------------------------------------------------------------+-----------+
1 row in set (30.62 sec)
So I noticed an improvement in 8.0.18.
Description: The str_to_date benchmark completes in the following times on Windows x64 release builds: 5.7.2: 30.86 sec 5.6.12: 30.22 sec 5.5.32: 15.11 sec 5.1.70: 12.64 sec 5.0.96: 12.87 sec How to repeat: select benchmark(100000000,str_to_date('2013','%Y')); Suggested fix: The problem is sprintf is too slow. Using a complex function such as sprintf doesn't make sense for such a simple format string "%04u-%02u-%02u". In 5.5 we used my_vsnprintf_ex which was a worthwhile optimization. But we can still do even better optimization than that.