| Bug #39936 | Merge view creating improper subquery - kills performance | ||
|---|---|---|---|
| Submitted: | 8 Oct 2008 17:17 | Modified: | 23 Oct 2008 21:19 |
| Reporter: | Boyd Hemphill | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.1.24 | OS: | Linux (Centos 5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | merge, performance, slow, Views | ||
[8 Oct 2008 17:25]
Valeriy Kravchuk
Thank you for a problem report. Please, provide a complete test case, with CREATE TABLE for all the tables involved and some data. Also, if possible, check with MySQL 6.0.6. It should already do a better job with subqeries.
[23 Oct 2008 15:39]
Boyd Hemphill
Here is a script. Note that execution time is very low because of limited amount of data, but the problem is still apparent from the explain plans. drop database if exists bug39936 ; create database bug39936 ; use bug39936 ; CREATE TABLE `major` ( `major_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `major` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`major_id`) ) ENGINE=InnoDB ; insert into `major`(`major_id`,`major`) values (1,'cc762b54') , (2,'9ed8479e') , (3,'70d39e4c') , (4,'bbd45bad') , (5,'d811f743') , (6,'c67e724d') , (7,'329f5105') , (8,'a5053d19') , (9,'47ac9506') , (10,'363ebcdd') ; CREATE TABLE `major_grad` ( `major_grad_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `school_id` int(10) unsigned NOT NULL, `major_id` int(10) unsigned NOT NULL, `school_year` smallint(5) unsigned DEFAULT NULL, `degree` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `grad_cnt` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`major_grad_id`), KEY `fk_major$major_grad` (`major_id`), KEY `fk_school$major_grad` (`school_id`), CONSTRAINT `fk_major$major_grad` FOREIGN KEY (`major_id`) REFERENCES `major` (`major_id`) ) ENGINE=InnoDB ; insert into `major_grad`(`major_grad_id`,`school_id`,`major_id`,`school_year`,`degree`,`grad_cnt`) values (97409,2,4,2006,'eef40a75',31) , (230444,3,4,2006,'cb0f3ac0',9) , (41704,4,4,2006,'cb0f3ac0',0) , (10852,5,4,2006,'cb0f3ac0',0) , (11420,6,8,2006,'03bde11d',18) , (203147,8,10,2006,'eef40a75',48) , (61017,10,8,2006,'052db5e8',1) , (51524,12,4,2006,'cb0f3ac0',7) , (11550,13,7,2006,'eef40a75',2) , (176363,14,4,2006,'58ab9c11',30) , (140053,15,4,2006,'052db5e8',19) , (243660,17,4,2006,'58ab9c11',6) , (12338,18,4,2006,'eef40a75',69) , (60761,24,1,2006,'052db5e8',10) , (147821,27,4,2006,'cb0f3ac0',13) , (12000,29,4,2006,'eef40a75',88) , (7207,30,7,2006,'eef40a75',67) , (149689,31,4,2006,'eef40a75',0) , (157019,32,4,2006,'eef40a75',98) , (244099,34,1,2006,'58ab9c11',6) , (178366,35,4,2006,'eef40a75',110) , (216953,36,4,2006,'eef40a75',122) , (168470,39,4,2006,'eef40a75',311) , (197834,41,4,2006,'eef40a75',48) , (42700,42,8,2006,'03bde11d',56) ; create or replace algorithm=undefined sql security invoker view v_major_school as select distinct mg.school_id as school_id, mg.major_id, m.major from major m, major_grad mg where m.major_id = mg.major_id ; select * from v_major_school where school_id = 24 and major_id = 1 ; /* +----+-------------+------------+------+---------------------+---------------------+---------+---------------------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------------+---------------------+---------+---------------------+------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 25 | Using where | | 2 | DERIVED | m | ALL | PRIMARY | NULL | NULL | NULL | 10 | Using temporary | | 2 | DERIVED | mg | ref | fk_major$major_grad | fk_major$major_grad | 4 | bug39936.m.major_id | 1 | Distinct | +----+-------------+------------+------+---------------------+---------------------+---------+---------------------+------+-----------------+ */ select distinct `mg`.`school_id` AS `school_id`, `mg`.`major_id` AS `major_id`, `m`.`major` AS `major` from (`major` `m` join `major_grad` `mg`) where (`m`.`major_id` = `mg`.`major_id`) and mg.school_id = 24 and m.major_id = 1 ; /* +----+-------------+-------+-------+------------------------------------------+----------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------------------------------+----------------------+---------+-------+------+-------------+ | 1 | SIMPLE | m | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | mg | ref | fk_major$major_grad,fk_school$major_grad | fk_school$major_grad | 4 | const | 1 | Using where | +----+-------------+-------+-------+------------------------------------------+----------------------+---------+-------+------+-------------+ */
[23 Oct 2008 19:19]
Sveta Smirnova
Thank you for the feedback. But why do you call VIEW with algorithm=undefined "merge view"? If I try to create same view using algorithm=merge I got correct warning "Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)", s temptable is really used when accessing the view as described at http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html So this looks like "Not a Bug" for me.
[23 Oct 2008 21:19]
Boyd Hemphill
Here is the text to the link included: QUOTE For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used. END QUOTE So, it should choose merge. Additionally if I do not specify the algorithm I get the same result reported. Additionally if I specify algorithm=merge I get the same issue. (change in line 66 of the script previously included)
Description: It appears that the server is doing a very poor job of rewriting the view. Here is an example: CREATE or replace ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v_major_school` AS SELECT distinct `rsmg`.`school_id` AS `school_id`, `rsmg`.`major_id` AS `major_id`, `m`.`major` AS `major` FROM ( `exp`.`raw_school_major_graduating` `rsmg` JOIN `exp`.`major` `m` ON ( (`m`.`major_id` = `rsmg`.`major_id`))) The view is accessed by another simple query: SELECT * FROM v_exp.`v_major_school` WHERE school_id = 2719 AND major_id = 223 ; It takes about 3 seconds to get a result Explain Plan: 'id' 'select_type' 'table' 'type' 'possible_keys' 'key' 'key_len' 'ref' 'rows' 'Extra' 1 'PRIMARY' '<derived2>' 'system' 1 '' 2 'DERIVED' 'm' 'const' 'PRIMARY' 'PRIMARY' '4' '' 1 '' 2 'DERIVED' 'rsmg' 'index_merge' 'fk_major$raw_school_major_graduating,fk_school$raw_school_major_graduating' 'fk_school$raw_school_major_graduating,fk_major$raw_school_major_graduating' '4,4' 1 'Using intersect(fk_school$raw_school_major_graduating,fk_major$raw_school_major_graduating); Using where; Using index' Notice the use of derived tables. I believe what should happen here is that the where clause should be concatenated to the end of the query. The explain plan seems to indicate a correlated subquery, so I changed the names of the columns in my view but this did not help. I suspect the actual column names are being used and this is why the optimizer might see them a correlating. Another interesting observation is that explain took just as long to run as the query itself. ========= Run the "manually merged query" SELECT sql_no_cache distinct `rsmg`.`school_id` AS `school_id`, `rsmg`.`major_id` AS `major_id`, `m`.`major` AS `major` FROM ( `exp`.`raw_school_major_graduating` `rsmg` JOIN `exp`.`major` `m` ON ( (`m`.`major_id` = `rsmg`.`major_id`))) WHERE (school_id = 2719) AND (rsmg.major_id = 223) Note the style of the query as this is the exact output for the view defn. Here is the explain plan 'id' 'select_type' 'table' 'type' 'possible_keys' 'key' 'key_len' 'ref' 'rows' 'Extra' 1 'SIMPLE' 'm' 'const' 'PRIMARY' 'PRIMARY' '4' 'const' 1 '' 1 'SIMPLE' 'rsmg' 'index_merge' 'fk_major$raw_school_major_graduating,fk_school$raw_school_major_graduating' 'fk_school$raw_school_major_graduating,fk_major$raw_school_major_graduating' '4,4' 1 'Using intersect(fk_school$raw_school_major_graduating,fk_major$raw_school_major_graduating); Using where; Using index' Note that all is right and good with the optimizer. I have now encountered this issue 4 different times. It makes the use of views impossible at this point. I can work around by having the developers use SQL in their application code but this is highly undesirable, thus it is marked S2. How to repeat: See above. If more detail is needed, please let me know. Suggested fix: Insure that the view performs as well or better than the SQL statement that represents its use.