Description:
The main problem - if add in Join on `OR`-condition, select become VERY slow. I realy have to use this condition.
How to repeat:
-- --------------------------------------------------------
--
-- `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?
