MySQL 5.0.3 sub query very slow?

hello,

got a situation with my newly installed 5.0.3:

WinXP pro, P4 2.4G 256MB DDRAM

table1 35,000 rows, myisam, columnx auto_increment
table2 40,000 rows, myisam, columny auto_increment

i executed the ff:

select * from table1 where columnz in (select columnz
from table2 where columnz='this value doesnt exist')

and it takes my computer almost dead when it is only
expected to yield no row.

i suspect on the structure of my tables.
do i need to index columnz?

i really need the subquery feature for my application.
any help is highly appreciated.

thanks



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MySQL 5.0.3 sub query very slow?

to be exact:

Empty set (34 min 0.27 sec)



--- Tata Respecia wrote:
> hello,
>
> got a situation with my newly installed 5.0.3:
>
> WinXP pro, P4 2.4G 256MB DDRAM
>
> table1 35,000 rows, myisam, columnx auto_increment
> table2 40,000 rows, myisam, columny auto_increment
>
> i executed the ff:
>
> select * from table1 where columnz in (select
> columnz
> from table2 where columnz='this value doesnt exist')
>
> and it takes my computer almost dead when it is only
> expected to yield no row.
>
> i suspect on the structure of my tables.
> do i need to index columnz?
>
> i really need the subquery feature for my
> application.
> any help is highly appreciated.
>
> thanks
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site!
> http://smallbusiness.yahoo.com/resources/
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
>
http://lists.mysql.com/win32?unsub=tatarespecia@yahoo.com
>
>



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MySQL 5.0.3 sub query very slow?

peter;

table1 and table2 have 4 and 5 mb size now.
im afraid i will be getting twice the size
and more and more on the near future...

but if there is no other way you can suggest,
i am forced to take the your suggestion.

thanks,

Tata

--- Peter Fleming wrote:
> Hi Tata
>
> Yes, you definitely want to add an index to columnz.
> Otherwise you will
> force a tablescan which will be extremely slow, like
> you are seeing.
>
> Peter :-)
>
> At 11:23 PM 3/28/2005, you wrote:
> >to be exact:
> >
> >Empty set (34 min 0.27 sec)
> >
> >
> >
> >--- Tata Respecia wrote:
> > > hello,
> > >
> > > got a situation with my newly installed 5.0.3:
> > >
> > > WinXP pro, P4 2.4G 256MB DDRAM
> > >
> > > table1 35,000 rows, myisam, columnx
> auto_increment
> > > table2 40,000 rows, myisam, columny
> auto_increment
> > >
> > > i executed the ff:
> > >
> > > select * from table1 where columnz in (select
> > > columnz
> > > from table2 where columnz='this value doesnt
> exist')
> > >
> > > and it takes my computer almost dead when it is
> only
> > > expected to yield no row.
> > >
> > > i suspect on the structure of my tables.
> > > do i need to index columnz?
> > >
> > > i really need the subquery feature for my
> > > application.
> > > any help is highly appreciated.
> > >
> > > thanks
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Small Business - Try our new resources
> site!
> > > http://smallbusiness.yahoo.com/resources/
> > >
> > > --
> > > MySQL Windows Mailing List
> > > For list archives: http://lists.mysql.com/win32
> > > To unsubscribe:
> > >
>
>http://lists.mysql.com/win32?unsub=tatarespecia@yahoo.com
> > >
> > >
> >
> >
> >
> >__________________________________
> >Do you Yahoo!?
> >Yahoo! Small Business - Try our new resources site!
> >http://smallbusiness.yahoo.com/resources/
> >
> >--
> >MySQL Windows Mailing List
> >For list archives: http://lists.mysql.com/win32
> >To unsubscribe:
>
http://lists.mysql.com/win32?unsub=peter@bluesbands.com
>
>



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MySQL 5.0.3 sub query very slow?

--=_alternative 0055883985256FD3_=
Content-Type: text/plain; charset="US-ASCII"

Indexes will help. To know if your query uses an index or not, use the
EXPLAIN command.

EXPLAIN select * from table1 where columnz in (select columnz from table2
where columnz='this value doesnt exist');

If this is a common query (you match columnz between the two tables
frequently), then I would make sure that columnz is the first column
listed in at least one index on both tables.

Actually, you do not *need* to use a subquery to get this same
information.

SELECT t1.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.columnz = t2.columnz
WHERE t2.columnz = 'this value doesnt exist';

is an equivalent query to the subquery version you posted. In fact, many
queries that use subqueries can be rewritten to use JOINs. Many times the
JOINed version will be faster (but not always). If you would like to try
converting your subquery into a form that uses JOINs and have problems
with the conversion, just post your original query to the list and we can
help you make the switch.



Tata Respecia wrote on 03/28/2005 11:23:55 PM:

> to be exact:
>
> Empty set (34 min 0.27 sec)
>
>
>
> --- Tata Respecia wrote:
> > hello,
> >
> > got a situation with my newly installed 5.0.3:
> >
> > WinXP pro, P4 2.4G 256MB DDRAM
> >
> > table1 35,000 rows, myisam, columnx auto_increment
> > table2 40,000 rows, myisam, columny auto_increment
> >
> > i executed the ff:
> >
> > select * from table1 where columnz in (select
> > columnz
> > from table2 where columnz='this value doesnt exist')
> >
> > and it takes my computer almost dead when it is only
> > expected to yield no row.
> >
> > i suspect on the structure of my tables.
> > do i need to index columnz?
> >
> > i really need the subquery feature for my
> > application.
> > any help is highly appreciated.
> >
> > thanks
> >
> >

You're welcome,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 0055883985256FD3_=--

RE: MySQL 5.0.3 sub query very slow?

I don't think you even *need* table2 either. Since the join is on
columnz and the WHERE clause says columnz is to be 'this value doesnt
exist' then I think the query just boils down to

SELECT *
FROM table1
WHERE columnz =3D 'this value doesnt exist';

This should be quite fast if you have an index on columnz.

Is there a typo in the original query? It seems you are making things
harder than necessary!

John Bonnett

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Wednesday, 30 March 2005 1:01 AM
To: Tata Respecia
Cc: win32@lists.mysql.com
Subject: Re: MySQL 5.0.3 sub query very slow?

Indexes will help. To know if your query uses an index or not, use the=20
EXPLAIN command.

EXPLAIN select * from table1 where columnz in (select columnz from
table2=20
where columnz=3D'this value doesnt exist');

If this is a common query (you match columnz between the two tables=20
frequently), then I would make sure that columnz is the first column=20
listed in at least one index on both tables.

Actually, you do not *need* to use a subquery to get this same=20
information.

SELECT t1.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.columnz =3D t2.columnz
WHERE t2.columnz =3D 'this value doesnt exist';

is an equivalent query to the subquery version you posted. In fact,
many=20
queries that use subqueries can be rewritten to use JOINs. Many times
the=20
JOINed version will be faster (but not always). If you would like to try

converting your subquery into a form that uses JOINs and have problems=20
with the conversion, just post your original query to the list and we
can=20
help you make the switch.



Tata Respecia wrote on 03/28/2005 11:23:55 PM:

> to be exact:
>=20
> Empty set (34 min 0.27 sec)
>=20
>=20
>=20
> --- Tata Respecia wrote:
> > hello,
> >=20
> > got a situation with my newly installed 5.0.3:
> >=20
> > WinXP pro, P4 2.4G 256MB DDRAM=20
> >=20
> > table1 35,000 rows, myisam, columnx auto_increment=20
> > table2 40,000 rows, myisam, columny auto_increment
> >=20
> > i executed the ff:
> >=20
> > select * from table1 where columnz in (select
> > columnz
> > from table2 where columnz=3D'this value doesnt exist')
> >=20
> > and it takes my computer almost dead when it is only
> > expected to yield no row.
> >=20
> > i suspect on the structure of my tables.
> > do i need to index columnz?=20
> >=20
> > i really need the subquery feature for my
> > application.
> > any help is highly appreciated.
> >=20
> > thanks
> >=20
> >=20

You're welcome,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org