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/
