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