List:General Discussion« Previous MessageNext Message »
From:Grant Giddens Date:December 21 2005 1:50pm
Subject:Re: Do I need to tweak my server variables for this SELECT statement?
View as plain text  
Hi James.
  
    Thanks for the tips.  I tried your below SQL call of quering just one column.  The
query didn't speed up.
  
  I think I am running out of RAM and thus caching the temp table to  disk.  My server is
currently using the default my.cnf file.   I will try the large and huge example .cnf
files to increate table  cache limits.  
  
  Like I mentioned before, I am tweaking the .cnf files by blind trial  and error.  I
would appreciate anyone with experience looking over  my current setup and proposed
setup.  I'm not sure how much RAM to  allocate to each server variable.
  
  
 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
  Thanks,
  Grant

James Harvard <james.lists.tech@stripped> wrote: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/mysql?unsub=1




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
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