Background: There are two view algorithms, temptable and merge. Merge merges view def with primary SELECT, which is fast. Temptable is same as subquery in from clause and is materialized befor join or filter from primary SELECT is applied.
If query needs a temp table, like for order by, temptable algorithm is used. If you can add an index to satisfy order by it will work, otherwise if view is used in ordered fashion by itself (no filter on it/no join) then create different view without order by to join to. Sent from my iPhone > On Jun 22, 2015, at 7:10 AM, Justin Swanhart <greenl...@gmail.com> wrote: > > This is now a merge view because you removed order by. > > Sent from my iPhone > >> On Jun 22, 2015, at 4:24 AM, Rhys Campbell <rhys.campb...@tradingscreen.com> >> wrote: >> >> Hello All, >> >> I had an interesting problem presented to me this morning. A seemingly >> simple query was running for ages and filling up /tmp causing MariaDB to >> crash. Now, this isn’t perhaps the best designed database but the query >> didn’t seem to be that bad. T1 is a table but t2 is a view (modified >> definitions provided below) >> >> SELECT SQL_NO_CACHE p.col1 >> FROM t1 p >> LEFT OUTER JOIN t2 ot >> ON p.col1 = ot.col2 >> LIMIT 500; >> >> This has the following explain plan… >> >> id >> select_type >> table >> type >> possible_keys >> key >> key_len >> ref >> rows >> filtered >> Extra >> 1 >> PRIMARY >> P1 >> index >> \N >> -- >> 4 >> \N >> 85977207 >> 100 >> Using index; Using temporary; Using filesort >> 1 >> PRIMARY >> p1 >> ref >> IX_1 >> IX_1 >> 8 >> -- >> 1 >> 100 >> Using where; Using index >> 3 >> DEPENDENT SUBQUERY >> p2 >> ref >> IX_1 >> IX_1 >> 8 >> -- >> 1 >> 100 >> Using where; Using index >> >> To rectify this I modified an index that was use by the view (note the >> key_len = 8 in above, went to 106 with the new index when EXPLAINed >> separately.). I also removed the ORDER BY from the view. After this the >> query performance much better returning in ~ 250 milliseconds. Removal of >> the ORDER By was primarily responsible for the performance improvement. >> However the EXPLAIN plan now returns this… >> >> id >> select_type >> table >> type >> possible_keys >> key >> key_len >> ref >> rows >> filtered >> Extra >> 1 >> PRIMARY >> p >> index >> \N >> idx >> 4 >> \N >> 85977207 >> 100 >> Using index >> >> >> Why is the view now missing from the EXPLAIN? Is this a bug or is there >> something else going on here? >> >> Cheers, >> >> Rhys >> >> CREATE TABLE `t1` ( >> `col1` BIGINT(20) NOT NULL, >> `col2` ENUM(val1, val2, val3) COLLATE latin1_bin NOT NULL, >> `col3` CHAR(2) CHARACTER SET latin1 NOT NULL, >> `col4` INT(10) UNSIGNED DEFAULT NULL, >> `col5` TINYINT(1) DEFAULT '1', >> `col6` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE >> CURRENT_TIMESTAMP, >> `col7` VARCHAR(100) CHARACTER SET latin1 DEFAULT NULL, >> `col8` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL, >> `col9` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, >> `col10` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL, >> `col11` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT >> NULL, >> `col12` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT >> NULL, >> `col13` DECIMAL(16,6) DEFAULT NULL, >> `col14` DECIMAL(16,6) DEFAULT NULL, >> `col15` BIGINT(20) DEFAULT NULL, >> `col16` BIGINT(20) DEFAULT NULL, >> `col17` BIGINT(20) DEFAULT NULL, >> `col18` BIGINT(20) DEFAULT NULL, >> `col19` CHAR(8) CHARACTER SET latin1 DEFAULT NULL, >> `col20` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col21` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col22` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col23` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col24` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col25` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col26` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col27` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col28` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col29` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> `col30` VARCHAR(45) CHARACTER SET latin1 DEFAULT NULL, >> PRIMARY KEY (`col1`), >> .. /* REMOVED **/ >> KEY `idx_lastupdated` (`col6`) >> ) ENGINE=TokuDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin >> `compression`='tokudb_zlib' >> >> The join view has the following definition… >> >> SELECT >> /* COLS REMOVED */ >> FROM `t2` `p1` >> WHERE (`p1`.`col1` = (SELECT >> `p2`.`col2 ` >> FROM `t2` `p2` >> WHERE ((`p2`.`col2` = `p1`.`mcol2`) >> AND (`p2`.`source` = 'src')) >> ORDER BY `p2`.`col3` DESC >> LIMIT 1)) >> ORDER BY `p1`.`col2`; >> >> >> >> Rhys Campbell >> Database Administrator >> TradingScreen, Inc. >> 23 York House, 5th Floor >> London WC2B 6UJ >> Email: rhys.campb...@tradingscreen.com >> >> Follow TradingScreen on Twitter , Facebook and our blog Trading Smarter >> This message is intended only for the recipient(s) named above and may >> contain confidential information. If you are not an intended recipient, you >> should not review, distribute or copy this message. Please notify the sender >> immediately by e-mail if you have received this message in error and delete >> it from your system. >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
