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, 5^th 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.