maria-discuss team mailing list archive

[Rhys Campbell <Rhys.Campbell@xxxxxxxxxxxxxxxxx>]

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<mailto:rhys.campbell@xxxxxxxxxxxxxxxxx>

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.