SQL optimization

[Dean Ware <d_ware@xxxxxxxxx>]

Hi, MySQL seems to really under perform when using joins.

Am I doing something wrong?

I have the following query:
SELECT DISTINCT Product.ProductID, Product.Title, ProductFormat.Price, 
ProductFormat.TradePrice, ProductFormat.ProductCode, ProductFormat.Format, 
ShoppingCart.Quantity
        FROM Product
        LEFT JOIN ProductFormat
        ON ProductFormat.ProductID = Product.ProductID
        LEFT JOIN ShoppingCart
        ON ProductFormat.ProductCode = ShoppingCart.ProductCode
        WHERE ShoppingCart.PersonID = 13;

Product has about 1000 rows
ProductFormat has about 2000 rows
ShoppingCart has about 20 rows

Thing is, this query takes on average 3 seconds to return about 4 records!!

my other queries seem to be in the tens of milliseconds.

Thing is, all my queries with JOINS in them exhibit this poor performance.

PersonID is the primary key in ShoppingCart and the big table Product. 
ProductFormat has ProductCode as its primary key.

Is there anything I can do about this?

Thanks in advance


Dean Ware

Web Developer

http://www.readingroom.com
Winner : Best Business to Business Website 2000-01
(Internet Business Awards sponsored by ntl)

Reading Room Ltd.
77 Dean Street
Soho
London
W1D 3SH
UK

Tel: +44 (0) 20 7734 9499
Fax: +44 (0) 20 7439 4190

The information transmitted is intended only for the person or entity to 
which it is addressed and may contain confidential and/or privileged 
material.  Any review, re-transmission, dissemination or other use of, or 
taking of any action in reliance upon, this information by persons or 
entities other than the intended recipient is prohibited.   If you received 
this in error, please contact the sender and delete the material from any 
computer.


---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread78062@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php