maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #01524
Re: MariaDB subquery
Hi James,
This looks like a different problem from MDEV-6041 or MDEV-6081.
I've tried to create a dataset to repeat the problem, but I didn't succeed.
Is it possible for you to upload the dataset that shows the problem? If you
don't want to share it publicly, it is possible to upload it to where only
MariaDB developers can access it: https://mariadb.com/kb/en/ftp/
If the data cannot be uploaded at all, could you share output of EXPLAIN when
it is run on real data (so that values of 'rows' are real) ?
On Tue, Apr 01, 2014 at 02:45:24PM +0100, James Qian Wang wrote:
>
> 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
> _______________________________________________
> 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
--
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog
References
