List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 14 2001 1:11pm
Subject:Re: select from a join (a better/Quicker way??)
View as plain text  
add buyer_name index for each table.

Simon Buchanan wrote:

> Huston we have a problem:
> 
> table1:
> 
> purchase_order (primary key)
> client_number
> buyer_name
> 
> table2:
> 
> client_number
> buyer_number
> buyer_name
> 
> (all three combine for the primary key for table2)
> 
> Query:
> 
> select table1.purchase_order, table1.buyer_name,
> table2.buyer_number from
> table1 inner join table1 on
> (table1.buyer_name = table2.buyer_name);
> 
> 
> This works, it takes 4 seconds with a limit 50...
> 
> but there is over 100,000 records in table1 and over 33,000 records 
> in table2, so the inner join has to create a temp table with 100,000 
> X 33,000 records(??? is this right) to do it. = 133mins.
> 
> Any info, pointers would be apreciated. Thanks
> 
> Simon Buchanan
> 
> ---- Technical Director -------------------------
> New Media Communications    http://newmedia.co.nz
> Contact: 07 928-3701  mailto:simon@stripped
> -------------------------------------------------
>      "Endless Loop: n., see  Loop, Endless"
> 
> 
> ---------------------------------------------------------------------
> 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 <mysql-thread85451@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
select from a join (a better/Quicker way??)Simon Buchanan14 Sep
  • Re: select from a join (a better/Quicker way??)Gerald Clark14 Sep