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 products.id 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

anyone?
Martin 
______________________________________________ 
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
> http://pastebin.com/m46cced58
> 
> I'm including the sql statement itself here as well:
> 
> select distinct products.id 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 products.id = 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: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
See how Windows connects the people, information, and fun that are part of your life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
Thread
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