Re: Very different behaviour executing stored procedures between 5.5 and 5.7

[Daniel Bradley]

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