List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:September 26 2008 3:47pm
Subject:RE: Speed up slow SQL statement.
View as plain text  
Hi Eric-

the immediate challenge is to fic the join statement so
make sure is indexed
make sure browse_nodes_to_products.product_id is indexed
make sure browse_nodes_to_products.browse_node_id  is indexed
make sure browse_nodes.amazon_id is indexed

there seems to be mapping/relationship challenge for your product to browse_node_id

which finally maps to amazon_id

would be simpler if is there any way you can carry the product_id thru 
from products table to
browser_nodes_to_products table
to browse_nodes table

Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 

> From: e.stewart@stripped
> To: mysql@stripped
> Subject: Speed up slow SQL statement.
> Date: Fri, 26 Sep 2008 10:42:07 -0400
> Good morning everyone,
> I've got a sql statement that is running quite slow. I've indexed  
> everything I can that could possibly be applicable but I can't seem to  
> speed it up.
> I've put up the table structures, row counts, the sql statement and  
> the explain dump of the sql statement all in paste online here
> I'm including the sql statement itself here as well:
> select distinct as id,
>    products.created_at as created_at,
>    products.asin as asin,
>    products.sales_rank as sales_rank,
>    products.points as points
> from products
> inner join (browse_nodes, browse_nodes_to_products) on
>    (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id  
> and = browse_nodes_to_products.product_id)
> where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10
>   order by products.sales_rank desc limit 10 offset 0;
> What I'm trying to accomplish with this is to get an ordered list of  
> unique products found under a category.
> Any ideas on how I could speed this up?
> Thanks in advance,
> Eric Stewart
> e.stewart@stripped
> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

See how Windows connects the people, information, and fun that are part of your life.
Speed up slow SQL statement.Eric Stewart26 Sep
  • RE: Speed up slow SQL statement.Martin Gainty26 Sep
    • Re: Speed up slow SQL statement.Eric Stewart29 Sep
      • Re: Speed up slow SQL statement.Rob Wultsch29 Sep
        • Re: Speed up slow SQL statement.Moon's Father25 Oct