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
