Do I need to tweak my server variables for this SELECT statement?

[Grant Giddens]

 Hi,
  
    For the first time, I'm working with a really large  database.  I have 1 SQL statement
that brings my server to it's  knees.  This setup is currently on my home development PC,
and not  in production.  The server is running apache, samba, and mysql  under gentoo
linux.  I'm the only user, so there is no vitually  load on the server.  The server has 1
Gig of ram.
  
  I've got 2 tables, one that holds a list of product, the other holds a  list of
categories that the product is associated with.  My SELECT  statment just grabs 10
products that are associated with a specific  category.  The product table has 650,000
rows and the category  table has 8,150,000 rows.
  
  My SELECT statement is:
  
  SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, 
pn_pricecompare_product.title, pn_pricecompare_product.prod_id, 
pn_pricecompare_product.image_small, pn_pricecompare_product.brand, 
pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn, 
pn_pricecompare_product.model, pn_pricecompare_product.artist, 
pn_pricecompare_product.author, pn_pricecompare_product.binding, 
pn_pricecompare_product.label, pn_pricecompare_product.audiencerating, 
pn_pricecompare_product.studio, pn_pricecompare_product.releasedate, 
pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate, 
pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex, 
pn_pricecompare_product.lowest_price,  pn_pricecompare_product.num_merchants FROM
pn_pricecompare_catprod,  pn_pricecompare_product WHERE
((pn_pricecompare_catprod.category =  '283155') AND (pn_pricecompare_catprod.asin = 
pn_pricecompare_product.asin)) ORDER BY  pn_pricecompare_product.salesrank ASC
 LIMIT 0,10  
  
  Sometimes this takes 10 minutes to execute.  When this occurs, I can hear the hard drive
thrashing.
  
  If I do an EXPLAIN, I get:
  
   table       type        possible_keys       key        key_len       ref       rows    
   Extra
  pn_pricecompare_catprod     ref      PRIMARY,asin     PRIMARY     4      const    
355416      Using where; Using index; Using temporary; Using f...
  pn_pricecompare_product     eq_ref      asin     asin     10     
pn_pricecompare_catprod.asin     1       
  
  
  When the query executes, and I check the processes, I see "Copying to tmp table on disk"
  
  After googling for this statement I found several pages that indicate I  might have to
tweak the my.cnf file.  I checked my my.cnf file,  and it's just the default file.  I
found the example huge, large,  medium, and small .cnf files and plan on using them to
try to optimize  my my.cnf file.  Other than trial and error, I really don't know  what
I'm doing.
  
  Here is a link to my current server variables:
  http://retailretreat.com/mysql/server_variables.php.htm
  
  Here is a link to my current my.cnf file:
  http://retailretreat.com/mysql/my.cnf.txt
  
  Here is a link to my proposed my.cnf file.  I'm not sure if there are any errors or
mistakes in the file.
  http://retailretreat.com/mysql/my-new.cnf.txt
  
  Am I right that I need to tweak the my.cnf file?  I think I  optimized my tables with
INDEXes correctly already.  What should I  look for first?
  
  Thanks,
  Grant

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com