MySQL Forums
Forum List  »  Stored Procedures

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
Re: Very different behaviour executing stored procedures between 5.5 and 5.7
115
February 17, 2017 06:34PM


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.