MySQL Forums
Forum List  »  Performance

Need help optimizing a query
Posted by: Martin
Date: October 18, 2004 10:43AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help optimizing a query
3800
October 18, 2004 10:43AM
2487
October 18, 2004 11:27AM
2465
October 18, 2004 08:53PM
2459
October 19, 2004 09:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.