maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx
>  
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp