From: Johan De Meersman
Date: August 17 2010 11:31am
Subject: Re: Extremly slow Join with 'OR'
List-Archive: http://lists.mysql.com/mysql/222547
Message-Id: <AANLkTikEHixCTLrh+EK2wKQmSDZn5AX_iT6AQhwhb=eW@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=0016e64f471a6545d1048e034a42

--0016e64f471a6545d1048e034a42
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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, =D0=92=D0=BB=D0=B0=D0=B4 =D0=A0 <vuliad@g=
mail.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=3DMyISAM  DEFAULT CHARSET=3Dcp1251 AUTO_INCREMENT=3D869 ;
>
> 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`=3D`l0`.`id`)
> LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=3D`l1`.`id`)
> LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=3D`l2`.`id`)
> LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=3D`l3`.`id`)
> WHERE  `l0`.`pid` =3D0
>
> id      select_type     table   type    possible_keys   key     key_len r=
ef
>     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`=3D`l0`.`id` or `l1`.`pid`=3D=
100)
> LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=3D`l1`.`id` or `l2`.`pid`=3D=
200)
> LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=3D`l2`.`id` or `l3`.`pid`=3D=
300)
> LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=3D`l3`.`id` or `l4`.`pid`=3D=
400)
> WHERE  `l0`.`pid` =3D0
>
> 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.)
>



--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e64f471a6545d1048e034a42--