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
