List:General Discussion« Previous MessageNext Message »
From:Allen Date:September 18 2003 6:13pm
Subject:Re: Slow query join problem
View as plain text  
Yes, you are correct.  I was thinking that wouldn't be the case, but it makes sense now. 
I added indexes and then the query returned in a few seconds.  Definitely have to have
indexes.

Thanks!

Dan Nelson wrote:

> In the last episode (Sep 18), Allen said:
> 
>>I have two tables.  One table is 13 million rows the other is about 
>>250,000.  I am trying to join the two to find users.  The tables are not 
>>indexed and I know that will effect the speed, but the join never 
>>completes.  I let it run for about 10 hours and the process was stuck in 
>>the "sending" state, which I don't really understand.  What is taking so 
>>long?  The join size should be no greater than 250,000 which is the size of 
>>the second table.  Yes???  
>>I am using the default join_buffer size, which seems to low.  Might this be 
>>the problem?
> 
> 
> You _have_ to add indexes.  Mysql is currently scanning the entire 250k
> table for each record in the big table to find matching records.  You
> can verify this by running "EXPLAIN SELECT .. etc" on your query and
> multiplying all the "rows" values together.
> 

-- 
Allen Brost
Motorola - DMTS 
Allen.Brost@stripped
Work: (847)-435-2019
Cell: (847)-878-7784

Thread
Slow query join problemAllen18 Sep
  • Re: Slow query join problemDan Nelson18 Sep
    • Re: Slow query join problemAllen18 Sep
  • Re: Slow query join problemJoseph Bueno18 Sep