MySQL Forums
Forum List  »  Performance

Re: Need help optimizing a query
Posted by: KimSeong Loh
Date: October 18, 2004 08:53PM

Can you remove the STRAIGHT_JOIN and see how MySQL optimise the query for you.

What are you trying to archive with the following, having the same date in <= and >=, isn't it equivalent to =,
AND DATE_FORMAT(orders.InstallDate, '%Y-%m-%d') >= '2004-10-02'
AND DATE_FORMAT(orders.InstallDate, '%Y-%m-%d') <= '2004-10-02';

What is the field type of orders.InstallDate, DATE or DATETIME?
Is it index?
Note that MySQL cannot use the index if you use a DATE_FORMAT function, try to rewrite it with orders.InstallDate only in the left hand side, can you use BETWEEN or something.

From the output of EXPLAIN,
time | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where
The field time.TimeId does not look like the PRIMARY KEY, can you index this field.

products | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where
The field products.ProductId does not look like the PRIMARY KEY, can you index this field.

If you are using a STRAIGHT_JOIN, you should put `status` before `time`.
Change
FROM `orders`, `customers`, `time`, `status`, `products`
to
FROM `orders`, `customers`, `status`, `time`, `products`

Options: ReplyQuote


Subject
Views
Written By
Posted
3615
October 18, 2004 10:43AM
2373
October 18, 2004 11:27AM
Re: Need help optimizing a query
2371
October 18, 2004 08:53PM
2362
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.