List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:October 25 2008 12:58am
Subject:Re: Speed up slow SQL statement.
View as plain text  
You may see the section named group by optimization on the document.

On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch <wultsch@stripped> 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 <e.stewart@stripped> 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: 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/
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
>
> --
> Rob Wultsch
> wultsch@stripped
> wultsch (aim)
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
Speed up slow SQL statement.Eric Stewart26 Sep
  • RE: Speed up slow SQL statement.Martin Gainty26 Sep
    • Re: Speed up slow SQL statement.Eric Stewart29 Sep
      • Re: Speed up slow SQL statement.Rob Wultsch29 Sep
        • Re: Speed up slow SQL statement.Moon's Father25 Oct