Glancing over things I suggest: ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id);
(if product_id has greater cardinality put that before browse_node_id) The syntax: 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) is pretty ugly in my opinion..... On Mon, Sep 29, 2008 at 7:10 AM, Eric Stewart <[EMAIL PROTECTED]> wrote: > Good morning everyone, > > products.id is defined as a PRIMARY KEY so it's index. > browse_nodes_to_products.product_id is defined as a INDEX so it's indexed. > browse_nodes_to_products.browse_node_id is defined as an INDEX so it's > indexed. > browse_nodes.amazon_id is defined as an INDEX so it's indexed. > > See http://pastebin.com/m46cced58 > It has complete table structures, row counts and EXPLAIN output of the SQL > statement I'm trying to optimize. > > I don't think I understand your question regarding carrying the product_id > through the relationship. This is a many to many relationship. A browse_node > can contain many products and a product can be in many browse_nodes. This is > achieved through a many to many join table browse_nodes_to_products. > > Further research into the SQL statement is revealing that a temp table is > being created and may be one of the reason it's slowing down. > > Any ideas how I can optimize this? > > Eric > > On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote: > >> >> 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/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
