maria-discuss team mailing list archive

[James Qian Wang <jwang25610@xxxxxxxxx>]

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