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: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > 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 > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > _________________________________________________________________ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
