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.net>wrote:
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 -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog