I'm new to mysql and have gotten most things to work satisfactorily thus
far. One of the things that is stumping me at the moment is how to
perform queries using joins and how to optimize them for speed.
the query is structured as follows:
SELECT products.return
FROM products,prodcat
WHERE products.prodno=prodcat.prodno
AND products.Store="0001"
AND products.Class="0002"
AND prodcat.category="Animal"
AND prodcat.subcategory="Bear"
ORDER BY products.Title
The above query takes approximately 3 seconds to complete (which is
better than the 27 seconds without indexes, i admit but still a little
slow). Is there a way to optimize this kind of search? Setup of the
tables used in this particular area are below:
Setup:
products table with the following fields:
-ID (primary key)
-prodno
-store
-class
-title
-designer
-sugretail
-discount price
-return
prodcat (contains categories associated with each product) with the
following fields:
-ID (primary key)
-prodno
-category
-subcategory
All fields except for ID fields and sugretail and discount price fields
are varchar fields. Category and subcategory fields are longest at 50
chars each.
In products, the ID field has it's own index, product number, store,
class, designer, and title are inside an index called index_product.
In prodcat, product number, category, and subcategory are part of an
index called index_category.
Thanks in advance for your attention and time.
Charley
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
