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.