I've recently been doing some big table query optimisation, but after getting the query tweaked to hit the right index the query time is under very livable-with, despite the fact that MySQL seems to be examining more rows for my query than for yours. However the 'rows' column of thhe explain output is just an estimate.
I'm not an expert on this subject, but I do have a couple of ideas that should only take you a couple of minutes to test, until a big-table-guru steps in. :-) You could try starting MySQL using the 'mysql/support-files/my-huge.cnf' example config file to see if that makes any difference. "This is for a large system with memory of 1G-2G where the system runs mainly MySQL." It may be you're running into a limit on the size of temporary table MySQL will build in RAM, so it's doing it on disc instead. I would try this first. Also - and this really is just a guess - I wonder whether part of the problem is that you're retrieving a lot of data (lots of columns) and then sorting the resulting huge temporary table, only to use the first 10 rows. Try just selecting just one row to see if that helps. If it does then you could maybe use a sub-query or application code to feed the list of 10 prod_id values into a query that gets all the columns you need. SELECT pn_pricecompare_product.prod_id 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 It might be the case that you could create an index that MySQL could use to optimise the 'order by', retrieving the rows in sorted order, but I'm not up on that sort of optimisation and don't know for sure - you'd have to check out the relevant manual section. Another factor may be the table format - fixed versus dynamic row length. If you need to come back on this maybe we could see a 'show create table' for the two tables? Also if you enable the slow query log, it tells you the actual number of rows examined - might be useful if you continue to have trouble. HTH & good luck, James Harvard > 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" > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
