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
>
