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]
