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
