List:General Discussion« Previous MessageNext Message »
From:Влад Р Date:August 17 2010 9:22am
Subject:Extremly slow Join with 'OR'
View as plain text  
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.)

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