Re: Need help optimizing a query

[KimSeong Loh]

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`