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:
None 
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:38] Shane Bester
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.
[20 Jul 2013 8:39] Shane Bester
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 11:10] Shane Bester
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.