List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:December 21 2005 11:19am
Subject:Re: Do I need to tweak my server variables for this SELECT
statement?
View as plain text  
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"
> 
Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec