MySQL Forums :: Stored Procedures :: Very different behaviour executing stored procedures between 5.5 and 5.7


Advanced Search

Re: Very different behaviour executing stored procedures between 5.5 and 5.7
Posted by: Daniel Bradley ()
Date: February 17, 2017 06:34PM

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

Options: ReplyQuote


Subject Views Written By Posted
Very different behaviour executing stored procedures between 5.5 and 5.7 417 Daniel Bradley 02/15/2017 10:43PM
Re: Very different behaviour executing stored procedures between 5.5 and 5.7 53 Peter Brawley 02/16/2017 01:31AM
Re: Very different behaviour executing stored procedures between 5.5 and 5.7 63 Daniel Bradley 02/17/2017 06:34PM
Re: Very different behaviour executing stored procedures between 5.5 and 5.7 45 Peter Brawley 02/17/2017 08:13PM


Sorry, only registered users may post in this forum.

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.