Re: Speed up slow SQL statement.
You may see the section named group by optimization on the document. On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch [EMAIL PROTECTED] wrote: 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Speed up slow SQL statement.
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]
Re: Speed up slow SQL statement.
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]
Speed up slow SQL statement.
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]
RE: Speed up slow SQL statement.
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/
