Need help optimizing a query

[Martin]

Hello all

I have a query that is giving me a lot of problems, I've been tweeking indexes and the query itself and I got this

EXPLAIN SELECT STRAIGHT_JOIN
orders.OrderId,
DATE_FORMAT(orders.OrderDate, '%m/%d/%Y') as OrderDate,
DATE_FORMAT(orders.InstallDate, '%m/%d/%Y') as InstallDate,
orders.UserId, orders.InstallerId, orders.promotion, orders.flag,
products.ProductDesc, customers.customerid, customers.Name,
customers.city, customers.class, time.TimeDesc, status.StatusDesc
FROM `orders`, `customers`, `time`, `status`, `products`
WHERE orders.CustomerId = customers.CustomerId
AND orders.StatusId = status.StatusId AND orders.ProductId = products.ProductId
AND orders.TimeId = time.TimeId
AND DATE_FORMAT(orders.InstallDate, '%Y-%m-%d') >= '2004-10-02'
AND DATE_FORMAT(orders.InstallDate, '%Y-%m-%d') <= '2004-10-02';
Rows: 5

table | type | possible_keys | key | key_len | ref | rows | Extra
orders | ALL | CustomerId,TimeId,StatusId,ProductId | NULL | NULL | NULL | 8665 | Using where
customers | eq_ref | PRIMARY | PRIMARY | 4 | orders.CustomerId | 1
time | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where
status | eq_ref | PRIMARY | PRIMARY | 4 | orders.StatusId | 1
products | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where

I will appreciate any ideas about how to improve performance given that the database has already 8000 records and growing. There are some where clausules that are not there but you can get the idea.

Thank you very much, I really appreciate your help

Martin



Edited 1 time(s). Last edit at 10/18/2004 11:25AM by Mike Hillyer.