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.com>wrote:
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.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?
_______________________________________________ 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