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




--
James Qian Wang
Mobile: 44 7986 099 233