Only if you want to see duplicate rows :-) On Tue, Aug 17, 2010 at 2:21 PM, Ananda Kumar <anan...@gmail.com> wrote:
> use UNION ALL ..instead of UNION for better performance... > > On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman <vegiv...@tuxera.be > >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, Влад Р <vul...@gmail.com> 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
