List:General Discussion« Previous MessageNext Message »
From:Eric Stewart Date:September 29 2008 2:10pm
Subject:Re: Speed up slow SQL statement.
View as plain text  
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/

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