Re: Very different behaviour executing stored procedures between 5.5 and 5.7
Hi Peter,
Thanks for your reply.
I've found one difference that caused quite a significant difference between the two VMs.
With this identified, I need to go back and investigate whether I am seeing any difference in behaviour regarding the SELECT being returned.
The identified difference is that it seems that with MySQL 5.5, when executing a SELECT from a view,
the system could detect that it only needed to select from one underlying table instead of generating the view.
For example, in MySQL 5.5, the following
IF EXISTS( SELECT * FROM view_notifications WHERE ID=$ID ) THEN
seems to have been optimised to do:
IF EXISTS( SELECT * FROM notifications WHERE ID=$ID ) THEN
Whereas this doesn't seem to be the case in MySQL 5.7.
Could this be an "sql_mode" issue? I've read through that documentation quite a bit for other reasons and I don't remember it mentioning anything related to this.
Both VMs are running within VMWare Fusion 7.1.1 on a MacBook Pro (Retina 13-inch, Early 2015) OS X El Capitan with 8GB Ram
Both VMs have 2GB of ram.
VM 1 with Ubuntu 14 LTS is running "mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3"
VM 2 with Ubuntu 16 LTS is running "mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper"
Note, adding or removing the "view_" (indicating use of the view) changes the runtime of VM2 from several seconds to over a minute.
No. I don't have convenient access to testing using a native OS.
Btw, I'm much happier now that I've found the issue above, was really panicking.
Cheers,
Daniel Bradley
Subject
Views
Written By
Posted
3620
February 15, 2017 10:43PM
757
February 16, 2017 01:31AM
Re: Very different behaviour executing stored procedures between 5.5 and 5.7
933
February 17, 2017 06:34PM
670
February 17, 2017 08:13PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.