Re: LEFT JOIN function locking up when using large database

[Rob Taft]

Thank you very much!  I am a first year software engineer so I'm still
learning :-)
I had a query that took 23 seconds, and now takes .01 sec.

Thanks again!

Rob Taft


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: LEFT JOIN function locking up when using large database

[Michael T. Babcock]

Rob Taft wrote:


I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll
call these table1 and table2. The query uses both tables:

SELECT  FROM table1 LEFT JOIN table2 ON (table1.ID =
table2.table1_ID) WHERE ;
 


Do you have indexes on these?

alter table2 add index table1_id_idx(table1_ID);

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: LEFT JOIN function locking up when using large database

[Stefan Hinz, iConnect \(Berlin\)]

Rob,

as Jennifer stated, the problem is that your query doesn't use indexes.

> SELECT  FROM table1 LEFT JOIN table2 ON
(table1.ID =
> table2.table1_ID) WHERE ;

 would be of interest here. If MySQL can't use indexes
here, it will have to scan all of table2 (the big one) to find out which
rows match.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Rob Taft" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 06, 2003 7:53 PM
Subject: LEFT JOIN function locking up when using large database


> I have 2 tables, one with 1,000 entries, the other with 10,000
entries. I'll
> call these table1 and table2. The query uses both tables:
>
> SELECT  FROM table1 LEFT JOIN table2 ON
(table1.ID =
> table2.table1_ID) WHERE ;
>
> table2.table1_ID is just an int that matches an ID from table1. This
way I
> can get several values from 2 tables with one query. This works great
when
> table 1 has 100 entries and table2 has 1,000 entries. But when i
increase
> both by a factor of 10, the query never returns anything. I let it sit
for
> 10 mins before giving up, and my CPU usage is at 100% the whole time.
Any
> suggestions as to what the problem is?
>
> I tried this on both 3.23 and 4.0.7 and got the same results.
>
> The [EMAIL PROTECTED] would not take my email.
>
> Rob Taft
> [EMAIL PROTECTED]
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: LEFT JOIN function locking up when using large database

[Jennifer Goodie]

Are you using indexes on your tables?  This does not sound like a mysql bug,
but rather a problem with your table structure or query.  I have seen much
more complicated joins work on much larger tables without any problem, as I
am sure many people on this list have as well.  You should run an explain on
your query to make sure it is utilizing indexes.

-Original Message-
From: Rob Taft [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 06, 2003 10:53 AM
To: [EMAIL PROTECTED]
Subject: LEFT JOIN function locking up when using large
database

I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll
call these table1 and table2. The query uses both tables:

SELECT  FROM table1 LEFT JOIN table2 ON (table1.ID =
table2.table1_ID) WHERE ;

table2.table1_ID is just an int that matches an ID from table1. This way I
can get several values from 2 tables with one query. This works great when
table 1 has 100 entries and table2 has 1,000 entries. But when i increase
both by a factor of 10, the query never returns anything. I let it sit for
10 mins before giving up, and my CPU usage is at 100% the whole time.  Any
suggestions as to what the problem is?

I tried this on both 3.23 and 4.0.7 and got the same results.

The [EMAIL PROTECTED] would not take my email.

Rob Taft
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php