List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:August 17 2010 12:31pm
Subject:Re: Extremly slow Join with 'OR'
View as plain text  
Only if you want to see duplicate rows :-)

On Tue, Aug 17, 2010 at 2:21 PM, Ananda Kumar <anandkl@stripped> wrote:

> use UNION ALL ..instead of UNION for better performance...
>
> On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman <vegivamp@stripped
> >wrote:
>
> > You may want to split of your or conditions into a separate query, and
> use
> > UNION.
> >
> > On Tue, Aug 17, 2010 at 11:22 AM, Влад Р <vuliad@stripped> wrote:
> >
> > > The main problem - if add in Join on `OR`-condition, select become
> > > VERY slow. I realy
> > > have to use this condition.
> > >
> > > -- --------------------------------------------------------
> > > --
> > > -- `tree_data`
> > > --
> > >
> > > CREATE TABLE IF NOT EXISTS `tree_data` (
> > >  `id` int(11) NOT NULL AUTO_INCREMENT,
> > >  `pid` int(11) NOT NULL,
> > >  PRIMARY KEY (`id`),
> > > ) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=869 ;
> > >
> > > testiong on 800 rows
> > >
> > > Simple tree join query
> > > First QUERY is:
> > >
> > > SELECT
> > > `l0`.`id` AS  'l0id',
> > > `l0`.`pid` AS  `l0pid` ,
> > > `l1`.`id` AS  'l1id',
> > > `l1`.`pid` AS  `l1pid` ,
> > > `l2`.`id` AS  'l2id',
> > > `l2`.`pid` AS  `l2pid` ,
> > > `l3`.`id` AS  'l3id',
> > > `l3`.`pid` AS  `l3pid` ,
> > > `l4`.`id` AS  'l4id',
> > > `l4`.`pid` AS  `l4pid`
> > > FROM  `tree_data` AS  `l0`
> > > LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id`)
> > > LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id`)
> > > LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id`)
> > > LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id`)
> > > WHERE  `l0`.`pid` =0
> > >
> > > id      select_type     table   type    possible_keys   key     key_len
> > ref
> > >     rows    Extra
> > > 1       SIMPLE  l0      ref     pid     pid     4       const   4
> > > 1       SIMPLE  l1      ref     pid     pid     4       test.l0.id
> >  9
> > > 1       SIMPLE  l2      ref     pid     pid     4       test.l1.id
> >  9
> > > 1       SIMPLE  l3      ref     pid     pid     4       test.l2.id
> >  9
> > > 1       SIMPLE  l4      ref     pid     pid     4       test.l3.id
> >  9
> > >
> > > time execution is 0.0069.  result 207 rows
> > >
> > > looking at other query
> > >
> > > SELECT
> > > `l0`.`id` AS  'l0id',
> > > `l0`.`pid` AS  `l0pid` ,
> > > `l1`.`id` AS  'l1id',
> > > `l1`.`pid` AS  `l1pid` ,
> > > `l2`.`id` AS  'l2id',
> > > `l2`.`pid` AS  `l2pid` ,
> > > `l3`.`id` AS  'l3id',
> > > `l3`.`pid` AS  `l3pid` ,
> > > `l4`.`id` AS  'l4id',
> > > `l4`.`pid` AS  `l4pid`
> > > FROM  `tree_data` AS  `l0`
> > > LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id` or
> `l1`.`pid`=100)
> > > LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id` or
> `l2`.`pid`=200)
> > > LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id` or
> `l3`.`pid`=300)
> > > LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id` or
> `l4`.`pid`=400)
> > > WHERE  `l0`.`pid` =0
> > >
> > > And now execution time is 0.1455!!!
> > > profiling gives: Sending data   0.137295
> > > And analize:
> > > id  select_type table   type  possible_keys     key     key_len ref
> > > rows    Extr
> > > 1       SIMPLE  l0      ref     pid     pid     4       const   4
> > > 1       SIMPLE  l1      ALL     pid     NULL    NULL    NULL    800
> > > 1       SIMPLE  l2      ALL     pid     NULL    NULL    NULL    800
> > > 1       SIMPLE  l3      ALL     pid     NULL    NULL    NULL    800
> > > 1       SIMPLE  l4      ALL     pid     NULL    NULL    NULL    800
> > >
> > > And what will happen if there will be 10000 rows?
> > >
> > > (In real i using this `or` condition for selecting to the tree nodes
> > > for current element id and id can be id from other nodes.)
> > >
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Thread
Extremly slow Join with 'OR'Влад Р17 Aug
  • Re: Extremly slow Join with 'OR'Johan De Meersman17 Aug
    • Re: Extremly slow Join with 'OR'Ananda Kumar17 Aug
      • Re: Extremly slow Join with 'OR'Johan De Meersman17 Aug
        • Problem with mytopCarlos Eduardo Caldi17 Aug
          • Re: Problem with mytopBaron Schwartz17 Aug
            • RE: Problem with mytopCarlos Eduardo Caldi18 Aug