[Maria-discuss] MariaDB subquery
Hi All,
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line.
Am 24.03.2014 10:01, schrieb James Qian Wang:
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line
well, withouzt showing that query and the table structures how do you imagine any answer not just blind guessing?
Hi All,
Here is the table structures and query:
CREATE TABLE `c` ( `cid` int(10) unsigned NOT NULL AUTO_INCREMENT, `c_outcome` int(10) DEFAULT NULL, KEY `c_outcome` (`c_outcome`), PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
CREATE TABLE `c_extra` ( `cid` int(10) unsigned NOT NULL, `last_update` datetime NOT NULL, PRIMARY KEY (`cid`), KEY `last_update` (`last_update`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLE `s_traces` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `msg` enum('unknown','bye','pro','cancel','hold') NOT NULL DEFAULT 'unknown', `sender` enum('unknown','abc','carrier') NOT NULL DEFAULT 'unknown', PRIMARY KEY (`id`), KEY `callid` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
explain SELECT IFNULL((SELECT CONCAT(t.sender, '|', t.msg) FROM `s_traces` t WHERE t.cid = c.cid ORDER BY t.`id` ASC LIMIT 1), 'unknown') AS terminate FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c ON (c.cid=x.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000;
MariaDB (5.5, 10.0.9, 10.0.10) explain results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 Using where 2 DEPENDENT SUBQUERY t index callid PRIMARY 4 NULL 1 Using where
MySQL results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 test.x.cid 1 Using where 2 DEPENDENT SUBQUERY t ref callid callid 4 test.c.cid 1 Using where; Using filesort
MariaDB is a lot slower than MySQL for this case.
Please shed some light. Is it possible to resolve this by tweaking /etc/my.cnf? Or this needs code changing?
Thanks a lot in advance James
On Mon, Mar 24, 2014 at 9:46 AM, Reindl Harald h.reindl@thelounge.netwrote:
Am 24.03.2014 10:01, schrieb James Qian Wang:
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line
well, withouzt showing that query and the table structures how do you imagine any answer not just blind guessing?
_______________________________________________ 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
Dear All,
Here is another query (table structures are the same as my previous email):
EXPLAIN EXTENDED SELECT IFNULL(CONCAT(t.sender, '|', t.msg), 'unknown') AS terminate, c.cid FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c USING (`cid`) LEFT JOIN s_traces t ON(c.cid = t.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000; SHOW WARNINGS\G
MySQL: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x index last_update last_update 5 NULL 1 100.00 Using where; Using index 1 SIMPLE c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 100.00 Using where 1 SIMPLE t ref callid callid 4 easycall.x.cid 1 100.00 Using where
MariaDB: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x index last_update last_update 8 NULL 1 100.00 Using where; Using index; Using temporary; Using filesort 1 SIMPLE c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 100.00 Using where 1 SIMPLE t ALL callid NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
Please help.
Thanks a lot in advance
On Mon, Mar 31, 2014 at 9:33 AM, James Qian Wang jwang25610@gmail.comwrote:
Hi All,
Here is the table structures and query:
CREATE TABLE `c` ( `cid` int(10) unsigned NOT NULL AUTO_INCREMENT, `c_outcome` int(10) DEFAULT NULL, KEY `c_outcome` (`c_outcome`), PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
CREATE TABLE `c_extra` ( `cid` int(10) unsigned NOT NULL, `last_update` datetime NOT NULL, PRIMARY KEY (`cid`), KEY `last_update` (`last_update`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLE `s_traces` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `msg` enum('unknown','bye','pro','cancel','hold') NOT NULL DEFAULT 'unknown', `sender` enum('unknown','abc','carrier') NOT NULL DEFAULT 'unknown', PRIMARY KEY (`id`), KEY `callid` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
explain SELECT IFNULL((SELECT CONCAT(t.sender, '|', t.msg) FROM `s_traces` t WHERE t.cid = c.cid ORDER BY t.`id` ASC LIMIT 1), 'unknown') AS terminate FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c ON (c.cid=x.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000;
MariaDB (5.5, 10.0.9, 10.0.10) explain results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 Using where 2 DEPENDENT SUBQUERY t index callid PRIMARY 4 NULL 1 Using where
MySQL results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 test.x.cid 1 Using where 2 DEPENDENT SUBQUERY t ref callid callid 4 test.c.cid 1 Using where; Using filesort
MariaDB is a lot slower than MySQL for this case.
Please shed some light. Is it possible to resolve this by tweaking /etc/my.cnf? Or this needs code changing?
Thanks a lot in advance James
On Mon, Mar 24, 2014 at 9:46 AM, Reindl Harald h.reindl@thelounge.netwrote:
Am 24.03.2014 10:01, schrieb James Qian Wang:
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line
well, withouzt showing that query and the table structures how do you imagine any answer not just blind guessing?
_______________________________________________ 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
-- James Qian Wang Mobile: 44 7986 099 233
Hi James,
This looks like a different problem from MDEV-6041 or MDEV-6081.
I've tried to create a dataset to repeat the problem, but I didn't succeed.
Is it possible for you to upload the dataset that shows the problem? If you don't want to share it publicly, it is possible to upload it to where only MariaDB developers can access it: https://mariadb.com/kb/en/ftp/
If the data cannot be uploaded at all, could you share output of EXPLAIN when it is run on real data (so that values of 'rows' are real) ?
On Tue, Apr 01, 2014 at 02:45:24PM +0100, James Qian Wang wrote:
Here is another query (table structures are the same as my previous email):
EXPLAIN EXTENDED SELECT IFNULL(CONCAT(t.sender, '|', t.msg), 'unknown') AS terminate, c.cid FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c USING (`cid`) LEFT JOIN s_traces t ON(c.cid = t.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000; SHOW WARNINGS\G
MySQL: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x index last_update last_update 5 NULL 1 100.00 Using where; Using index 1 SIMPLE c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 100.00 Using where 1 SIMPLE t ref callid callid 4 easycall.x.cid 1 100.00 Using where
MariaDB: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE x index last_update last_update 8 NULL 1 100.00 Using where; Using index; Using temporary; Using filesort 1 SIMPLE c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 100.00 Using where 1 SIMPLE t ALL callid NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
Please help.
Thanks a lot in advance
On Mon, Mar 31, 2014 at 9:33 AM, James Qian Wang jwang25610@gmail.comwrote:
Hi All,
Here is the table structures and query:
CREATE TABLE `c` ( `cid` int(10) unsigned NOT NULL AUTO_INCREMENT, `c_outcome` int(10) DEFAULT NULL, KEY `c_outcome` (`c_outcome`), PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
CREATE TABLE `c_extra` ( `cid` int(10) unsigned NOT NULL, `last_update` datetime NOT NULL, PRIMARY KEY (`cid`), KEY `last_update` (`last_update`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLE `s_traces` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `msg` enum('unknown','bye','pro','cancel','hold') NOT NULL DEFAULT 'unknown', `sender` enum('unknown','abc','carrier') NOT NULL DEFAULT 'unknown', PRIMARY KEY (`id`), KEY `callid` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
explain SELECT IFNULL((SELECT CONCAT(t.sender, '|', t.msg) FROM `s_traces` t WHERE t.cid = c.cid ORDER BY t.`id` ASC LIMIT 1), 'unknown') AS terminate FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c ON (c.cid=x.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000;
MariaDB (5.5, 10.0.9, 10.0.10) explain results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 Using where 2 DEPENDENT SUBQUERY t index callid PRIMARY 4 NULL 1 Using where
MySQL results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 test.x.cid 1 Using where 2 DEPENDENT SUBQUERY t ref callid callid 4 test.c.cid 1 Using where; Using filesort
MariaDB is a lot slower than MySQL for this case.
Please shed some light. Is it possible to resolve this by tweaking /etc/my.cnf? Or this needs code changing?
Thanks a lot in advance James
On Mon, Mar 24, 2014 at 9:46 AM, Reindl Harald h.reindl@thelounge.netwrote:
Am 24.03.2014 10:01, schrieb James Qian Wang:
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line
well, withouzt showing that query and the table structures how do you imagine any answer not just blind guessing?
_______________________________________________ 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
-- James Qian Wang Mobile: 44 7986 099 233
-- James Qian Wang Mobile: 44 7986 099 233
_______________________________________________ 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
Hi,
For the record:
Analyzing this, I've found https://mariadb.atlassian.net/browse/MDEV-6041 and https://mariadb.atlassian.net/browse/MDEV-6081.
Both are now fixed in MariaDB 10.0.
On Mon, Mar 31, 2014 at 09:33:17AM +0100, James Qian Wang wrote:
Here is the table structures and query:
CREATE TABLE `c` ( `cid` int(10) unsigned NOT NULL AUTO_INCREMENT, `c_outcome` int(10) DEFAULT NULL, KEY `c_outcome` (`c_outcome`), PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
CREATE TABLE `c_extra` ( `cid` int(10) unsigned NOT NULL, `last_update` datetime NOT NULL, PRIMARY KEY (`cid`), KEY `last_update` (`last_update`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLE `s_traces` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `msg` enum('unknown','bye','pro','cancel','hold') NOT NULL DEFAULT 'unknown', `sender` enum('unknown','abc','carrier') NOT NULL DEFAULT 'unknown', PRIMARY KEY (`id`), KEY `callid` (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
explain SELECT IFNULL((SELECT CONCAT(t.sender, '|', t.msg) FROM `s_traces` t WHERE t.cid = c.cid ORDER BY t.`id` ASC LIMIT 1), 'unknown') AS terminate FROM `c_extra` x FORCE INDEX (`last_update`) INNER JOIN `c` c ON (c.cid=x.cid) WHERE 1 AND x.last_update >= '2014-03-20 11:21:08' AND (c_outcome IS NULL OR c_outcome NOT IN (104,105,106,111)) ORDER BY last_update LIMIT 0,1000;
MariaDB (5.5, 10.0.9, 10.0.10) explain results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 easycall.x.cid 1 Using where 2 DEPENDENT SUBQUERY t index callid PRIMARY 4 NULL 1 Using where
MySQL results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x index last_update last_update 8 NULL 1 Using where; Using index 1 PRIMARY c eq_ref PRIMARY,c_outcome PRIMARY 4 test.x.cid 1 Using where 2 DEPENDENT SUBQUERY t ref callid callid 4 test.c.cid 1 Using where; Using filesort
MariaDB is a lot slower than MySQL for this case.
Please shed some light. Is it possible to resolve this by tweaking /etc/my.cnf? Or this needs code changing?
Thanks a lot in advance James
On Mon, Mar 24, 2014 at 9:46 AM, Reindl Harald h.reindl@thelounge.netwrote:
Am 24.03.2014 10:01, schrieb James Qian Wang:
I have a query which shows type REF when explained in MySQL 5.5/5.6.
However, the same query shows type INDEX in MariaDB 10.0.9
I.e. the query is a lot slower in MariaDB.
Any one has the similar experience please?
Please drop me a line
well, withouzt showing that query and the table structures how do you imagine any answer not just blind guessing?
BR Sergei
participants (3)
-
James Qian Wang -
Reindl Harald -
Sergey Petrunia