maria-discuss team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi,

For the record:

Analyzing this, I've found https://mariadb.atlassian.net/browse/MDEV-6041 and
https://mariadb.atlassian.net/browse/MDEV-6081.

Both are now fixed in MariaDB 10.0.

On Mon, Mar 31, 2014 at 09:33:17AM +0100, James Qian Wang wrote:
> 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?
> >


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog