From: Martin Gainty
Date: September 26 2008 3:47pm
Subject: RE: Speed up slow SQL statement.
List-Archive: http://lists.mysql.com/mysql/214680
Message-Id: <BLU142-W14742A48C016E1C2A3FA1FAE470@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_fbe9f5e0-8507-4c55-bd54-70ec0641c62b_"

--_fbe9f5e0-8507-4c55-bd54-70ec0641c62b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


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=20
from products table to
browser_nodes_to_products table
to browse_nodes table

anyone?
Martin=20
______________________________________________=20
Disclaimer and confidentiality note=20
Everything in this e-mail and any attachments relates to the official busin=
ess of Sender. This transmission is of a confidential nature and Sender doe=
s not endorse distribution to any party other than intended recipient. Send=
er does not necessarily endorse content contained within this transmission.=
=20


> From: e.stewart@stripped
> To: mysql@stripped
> Subject: Speed up slow SQL statement.
> Date: Fri=2C 26 Sep 2008 10:42:07 -0400
>=20
> Good morning everyone=2C
>=20
> I've got a sql statement that is running quite slow. I've indexed =20
> everything I can that could possibly be applicable but I can't seem to =20
> speed it up.
>=20
> I've put up the table structures=2C row counts=2C the sql statement and =
=20
> the explain dump of the sql statement all in paste online here http://pas=
tebin.com/m46cced58
>=20
> I'm including the sql statement itself here as well:
>=20
> select distinct products.id as id=2C
>    products.created_at as created_at=2C
>    products.asin as asin=2C
>    products.sales_rank as sales_rank=2C
>    products.points as points
> from products
> inner join (browse_nodes=2C browse_nodes_to_products) on
>    (browse_nodes.amazon_id =3D browse_nodes_to_products.browse_node_id =20
> and products.id =3D browse_nodes_to_products.product_id)
> where browse_nodes.lft >=3D 5 and browse_nodes.rgt <=3D 10
>   order by products.sales_rank desc limit 10 offset 0=3B
>=20
>=20
> What I'm trying to accomplish with this is to get an ordered list of =20
> unique products found under a category.
>=20
> Any ideas on how I could speed this up?
>=20
> Thanks in advance=2C
>=20
> Eric Stewart
> e.stewart@stripped
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped=
om
>=20

_________________________________________________________________
See how Windows connects the people=2C information=2C and fun that are part=
 of your life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/=

--_fbe9f5e0-8507-4c55-bd54-70ec0641c62b_--