Bug #80505 mysql server 5.7.11 view performance issue
Submitted: 25 Feb 2016 9:43 Modified: 25 Feb 2016 14:53
Reporter: mohamed atef Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.11 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: view performance issue

[25 Feb 2016 9:43] mohamed atef
Description:
server 5.7.11 perform slowly when select from view with subquery
although server 5.7.10 with the same view perform fast
and the result set is the same data fetched

this example
drop database mytest;
create database mytest character set 'utf8' collate 'utf8_unicode_ci';
use mytest;
CREATE TABLE mytest.`products` (
  `ID` BIGint(20) NOT NULL AUTO_INCREMENT,
  `PRODUCT` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  
  `BASE_PRICE` double(10,4) NOT NULL DEFAULT '0.0000',
  
  `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATE_MODIFIED` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `USER_ID` INT  NOT NULL,
 
  PRIMARY KEY (`ID`),
  UNIQUE KEY `PRODUCT_UNIQUE` (`PRODUCT`) USING BTREE,
  
  KEY `USER_ID` (`USER_ID`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE mytest.`BRANCHES` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `BRANCH` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATE_MODIFIED` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `USER_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `BRANCH_UNIQUE` (`BRANCH`) USING BTREE,
  KEY `BUSER_ID` (`USER_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE mytest.`WISH_LIST` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `PRODUCT_ID` INT NOT NULL,
  `BRANCH_ID` INT NOT NULL,
  `DESTINATION_BRANCH_ID` INT NOT NULL,
  `REQUEST_QTY` INT NOT NULL,
    `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATE_MODIFIED` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `USER_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `WPRODUCT_ID` (`PRODUCT_ID`) USING BTREE,
  KEY `WBRANCH_ID` (`BRANCH_ID`) USING BTREE,
   KEY `WDESTINATION_BRANCH_ID`(`DESTINATION_BRANCH_ID`)  USING BTREE,
  KEY `WUSER_ID` (`USER_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `BRANCHES_STOCKS` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `PRODUCT_ID` bigint(20) DEFAULT NULL,
  `STOCK_QTY` int NOT NULL,
  `BRANCH_ID` INT NOT NULL,
  `USER_ID` INT NOT NULL,
  `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATE_MODIFIED` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`ID`),
  KEY `BSUSER_ID` (`USER_ID`) USING BTREE,
  KEY `BSBRANCH_ID` (`BRANCH_ID`) USING BTREE,
   KEY `BSPRODUCT_ID` (`PRODUCT_ID`) USING BTREE
 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `wish_list_view` AS
    SELECT 
        `wish_list`.`ID` AS `ID`,
        `wish_list`.`PRODUCT_ID` AS `PRODUCT_ID`,
        `products`.`PRODUCT` AS `PRODUCT`,
        `wish_list`.`REQUEST_QTY` AS `REQUEST_QTY`,
        `wish_list`.`BRANCH_ID` AS `BRANCH_ID`,
        `branches`.`BRANCH` AS `BRANCH`,
        `wish_list`.`DESTINATION_BRANCH_ID` AS `DESTINATION_BRANCH_ID`,
        IFNULL((SELECT 
                        SUM(`branches_stocks`.`STOCK_QTY`)
                    FROM
                        `branches_stocks`
                    WHERE
                        ((`branches_stocks`.`BRANCH_ID` = `wish_list`.`BRANCH_ID`)
                            AND (`branches_stocks`.`PRODUCT_ID` = `wish_list`.`PRODUCT_ID`))),
                0) AS `BRANCH_AVAILABLE_STOCK`,
        IFNULL((SELECT 
                        SUM(`branches_stocks`.`STOCK_QTY`)
                    FROM
                        `branches_stocks`
                    WHERE
                        ((`branches_stocks`.`BRANCH_ID` = `wish_list`.`DESTINATION_BRANCH_ID`)
                            AND (`branches_stocks`.`PRODUCT_ID` = `wish_list`.`PRODUCT_ID`))),
                0) AS `DESTINATION_AVAILABLE_STOCK`,
        IFNULL((SELECT 
                        SUM(`branches_stocks`.`STOCK_QTY`)
                    FROM
                        `branches_stocks`
                    WHERE
                        ((`branches_stocks`.`BRANCH_ID` <> `wish_list`.`DESTINATION_BRANCH_ID`)
                            AND (`branches_stocks`.`BRANCH_ID` <> `wish_list`.`BRANCH_ID`)
                            AND (`branches_stocks`.`PRODUCT_ID` = `wish_list`.`PRODUCT_ID`))),
                0) AS `OTHERS_AVAILABLE_STOCK`,
        `wish_list`.`USER_ID` AS `USER_ID`
    FROM
        ((`wish_list`
        JOIN `products` ON ((`wish_list`.`PRODUCT_ID` = `products`.`ID`)))
        JOIN `branches` ON ((`branches`.`ID` = `wish_list`.`BRANCH_ID`)))
    ORDER BY `products`.`PRODUCT`

explain extened select * from wish_list_view 
in both servers

How to repeat:
view performance issue
[25 Feb 2016 9:44] mohamed atef
extended explain for the query in server 5.7.11

Attachment: EXTENDED EXPLAIN DEIVED_MERGE ON MYSQL 5.7.11.html (text/html), 4.63 KiB.

[25 Feb 2016 9:45] mohamed atef
extended explain for query in server 5.7.10 and optimizer dervied_merge is set to off

Attachment: EXTENDED EXPLAIN derived_merge off 5.7.10.html (text/html), 4.63 KiB.

[25 Feb 2016 9:45] mohamed atef
extended explain for query in server 5.7.10 and optimizer dervied_merge is set to on

Attachment: EXTENDED EXPLAIN derived_merge on 5.7.10.html (text/html), 4.12 KiB.

[25 Feb 2016 12:33] Miguel Solorzano
Thank you for the bug report. This bug isn't duplicate/related to the bug http://bugs.mysql.com/bug.php?id=80470 you have already reported?. Thanks.
[25 Feb 2016 14:53] Miguel Solorzano
Thank you for the feedback.