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.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
Sometimes this takes 10 minutes to execute. When this occurs, I can hear the hard drive
If I do an EXPLAIN, I get:
table type possible_keys key key_len ref rows
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
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
Here is a link to my current server variables:
Here is a link to my current my.cnf file:
Here is a link to my proposed my.cnf file. I'm not sure if there are any errors or
mistakes in the file.
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?
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around