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 <jwang25...@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.rei...@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
_______________________________________________
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
