maria-discuss team mailing list archive

[James Qian Wang <jwang25610@xxxxxxxxx>]

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@xxxxxxxxx>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@xxxxxxxxxxxxx>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@xxxxxxxxxxxxxxxxxxx
>> 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