Extremly slow Join with "OR"

--000e0cd348626625ac048e017d85
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0cd348626625ac048e017d85--

Re: Extremly slow Join with "OR"

--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, Влад Р 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--

Re: Extremly slow Join with "OR"

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

use UNION ALL ..instead of UNION for better performance...

On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman wrot=
e:

> You may want to split of your or conditions into a separate query, and us=
e
> UNION.
>
> On Tue, Aug 17, 2010 at 11:22 AM, Влад Р @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=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
> 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`=3D`l0`.`id` or `l1`.`pid`=
=3D100)
> > LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=3D`l1`.`id` or `l2`.`pid`=
=3D200)
> > LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=3D`l2`.`id` or `l3`.`pid`=
=3D300)
> > LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=3D`l3`.`id` or `l4`.`pid`=
=3D400)
> > 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.)
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--001485f78c0a263070048e03fecf--

Re: Extremly slow Join with "OR"

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

Only if you want to see duplicate rows :-)

On Tue, Aug 17, 2010 at 2:21 PM, Ananda Kumar wrote:

> use UNION ALL ..instead of UNION for better performance...
>
> On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman > >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, Влад Р ad@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=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_l=
en
> > 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`=3D`l0`.`id` or
> `l1`.`pid`=3D100)
> > > LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=3D`l1`.`id` or
> `l2`.`pid`=3D200)
> > > LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=3D`l2`.`id` or
> `l3`.`pid`=3D300)
> > > LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=3D`l3`.`id` or
> `l4`.`pid`=3D400)
> > > 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.)
> > >
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



--=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

--001636ed6918929d53048e0422e5--

Problem with mytop

--_5db5f7a9-1139-4f0d-a9c9-2303c7670a5e_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



If sombody can help me I'll be gratefull

I have a problem with mytop1.6 on Mandriva 2010.1=2C when I install it on a=
Slave server mytop works=2C
but when I install it on a Master Server don't works=2C don't display the q=
ueries.

I use mysql version 5.0.77
and linux mandriva 2010.1

Very Thanks

Carlos Caldi - DBA =

--_5db5f7a9-1139-4f0d-a9c9-2303c7670a5e_--

Re: Problem with mytop

Carlos,

Have you tried innotop instead? It's a better replacement for mytop.
(I wrote it.)

- Baron

On Tue, Aug 17, 2010 at 8:50 AM, Carlos Eduardo Caldi
wrote:
>
>
> If sombody can help me I'll be gratefull
>
> I have a problem with mytop1.6 on Mandriva 2010.1, when I install it on a=
Slave server mytop works,
> but when I install it on a Master Server don't works, don't display the q=
ueries.
>
> I use mysql version 5.0.77
> and linux mandriva 2010.1
>
> Very Thanks
>
> Carlos Caldi - =A0DBA



--=20
Baron Schwartz
Percona Inc
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Problem with mytop

--_f35ef267-d8e3-405b-a6ca-a2b692ed972e_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi Baron

This tool works better than mytop=2C you solved my problem=2C thanks a lot


Carlos



> Date: Tue=2C 17 Aug 2010 10:41:23 -0400
> Subject: Re: Problem with mytop
> From: baron@xaprb.com
> To: mysql@lists.mysql.com
>=20
> Carlos=2C
>=20
> Have you tried innotop instead? It's a better replacement for mytop.
> (I wrote it.)
>=20
> - Baron
>=20
> On Tue=2C Aug 17=2C 2010 at 8:50 AM=2C Carlos Eduardo Caldi
> wrote:
> >
> >
> > If sombody can help me I'll be gratefull
> >
> > I have a problem with mytop1.6 on Mandriva 2010.1=2C when I install it =
on a Slave server mytop works=2C
> > but when I install it on a Master Server don't works=2C don't display t=
he queries.
> >
> > I use mysql version 5.0.77
> > and linux mandriva 2010.1
> >
> > Very Thanks
> >
> > Carlos Caldi - DBA
>=20
>=20
>=20
> --=20
> Baron Schwartz
> Percona Inc
> Consulting=2C Training=2C Support & Services for MySQL
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dce_caldi@hotmail.=
com
>=20
=

--_f35ef267-d8e3-405b-a6ca-a2b692ed972e_--