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
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