[Maria-discuss] View missing from EXPLAIN after ORDER BY removed from definition
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.campbell@tradingscreen.com<mailto:rhys.campbell@tradingscreen.com> Follow TradingScreen on Twitter<http://twitter.com/#!/TradingScreen> , Facebook<http://www.facebook.com/pages/TradingScreen/214046251945650> and our blog Trading Smarter<tradingsmarter.tradingscreen.com> 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.
On Mon, Jun 22, 2015 at 11:24:39AM +0000, Rhys Campbell 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;
So, ther query * uses LEFT JOIN * Doesn't use any columns from table 'ot' except in the ON clause. * The ON clause has a restriction in form ot.col2=... Does ot.col2 refer to the primary key of the VIEW's underlying table? If yes, then the query satisifies conditions for the Table Elimination optimization. Table Elimination removes t2 and its attached subquery from the query plan. If not, could you try the query after set optimizer_switch='table_elimination=off', and/or post the output of EXPLAIN EXTENDED; SHOW WARNINGS?
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.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Hi Sergey, Ah. That makes a lot of sense, but... Ot.col2 doesn't refer to the PK of the underlying table but it is at the head of a unique index (multiple columns) Value of optimizer_switch... index_merge=ON,index_merge_union=ON,index_merge_sort_union=ON,index_merge_intersection=ON,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=ON,derived_merge=ON,derived_with_keys=ON,firstmatch=ON,loosescan=ON,materialization=ON,in_to_exists=ON,semijoin=ON,partial_match_rowid_merge=ON,partial_match_table_scan=ON,subquery_cache=ON,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=ON,semijoin_with_cache=ON,join_cache_incremental=ON,join_cache_hashed=ON,join_cache_bka=ON,optimize_join_buffer_size=off,table_elimination=ON,extended_keys=ON,exists_to_in=ON When I set the table_elimination flag to OFF we get back to the original 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 So it's a pretty nonsensical query anyway but why is the ORDER BY making the table elimination impossible? Cheers, Rhys -----Original Message----- From: Sergey Petrunia [mailto:sergey@mariadb.com] Sent: 22 June 2015 14:16 To: Rhys Campbell Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] View missing from EXPLAIN after ORDER BY removed from definition On Mon, Jun 22, 2015 at 11:24:39AM +0000, Rhys Campbell 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;
So, ther query * uses LEFT JOIN * Doesn't use any columns from table 'ot' except in the ON clause. * The ON clause has a restriction in form ot.col2=... Does ot.col2 refer to the primary key of the VIEW's underlying table? If yes, then the query satisifies conditions for the Table Elimination optimization. Table Elimination removes t2 and its attached subquery from the query plan. If not, could you try the query after set optimizer_switch='table_elimination=off', and/or post the output of EXPLAIN EXTENDED; SHOW WARNINGS?
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.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
On Mon, Jun 22, 2015 at 01:33:20PM +0000, Rhys Campbell wrote:
Ah. That makes a lot of sense, but...
Ot.col2 doesn't refer to the PK of the underlying table but it is at the head of a unique index (multiple columns)
So, the outer query is: t1 p LEFT OUTER JOIN t2 ot ON p.col1 = ot.col2 And the t2 is a VIEW defined as: SELECT /* COLS REMOVED */ FROM `t2` `p1` WHERE (`p1`.`col1` = (SELECT ...)) Then, t2.col2= ... -- ON expression t2.col1= ... -- WHERE in the VIEW definition. Is the unique index defined over (col2, col1) ? Table Elimination kicks in only when the optimizer figures out that table t2 can have at most one matching record for each record of t1. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
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.Campbell@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.campbell@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
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 <greenlion@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.Campbell@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.campbell@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
participants (3)
-
Justin Swanhart -
Rhys Campbell -
Sergey Petrunia