List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:September 29 2008 8:44pm
Subject:Re: Speed up slow SQL statement.
View as plain text  
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)
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