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.)
