List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 18 2003 3:37pm
Subject:Re: Slow query join problem
View as plain text  
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.

-- 
	Dan Nelson
	dnelson@stripped
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