Query is really slow

[Mike]

I am making an order system and I have 20,000 orders and 100,000 line
items. I keep track of the status for each order so there will me more
then 1 status for each order. I want to check to see if the status ID
is not equal to 12 when ordering by the status date. I am not check for
the 12 value and it's taking for ever to load. Do you know what the
problem is?

select line_ord.row_ID, line_ord.record, customer.customer_type,
customer.company_name,
customer.company_abrv, line_ord.item, line_ord.`list`,
line_ord.itemqty, orders.casedisc,
orders.fhdisc, orders.sstopdisc, orders.ex_rate, line_ord.date_,
line_ord.moddate, line_ord.delrev
from mott.line_ord
INNER JOIN mott.orders ON orders.record = line_ord.record
INNER JOIN mott.customer ON customer.customer_ID = orders.custnum
LEFT OUTER JOIN orders.status ON status.record = line_ord.record
where (date_ between '2005-11-01' and '2005-12-24') or
((moddate between '2005-11-01' and '2005-12-24') and delrev > 0)
and not line_ord.list = 0
group by row_ID
order by date_ asc, status.date desc
PS delrev is when the line item has been deleted in the order's
revision. So I am checking for added and deleted items
Thank you,

Mike

[Bill Karwin]

"Mike" <po******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

it's taking for ever to load. Do you know what the problem is?

You can get some information about the query performance and which indexes
it's using to speed up the query by using the EXPLAIN keyword.
See: http://dev.mysql.com/doc/refman/5.0/en/explain.html

Make sure you have indexes defined for the fields used in join conditions.
It may also improve things if you create indexes on fields on which you
sort, and the fields used in the WHERE clause.

Also I'm not sure why you're using GROUP BY in your query. You aren't
calculating any aggregate functions, and you're grouping by a field which I
infer is a unique field. Either the GROUP BY has no effect in this case, or
else it's collapsing some rows to one (a peculiar use of GROUP BY in MySQL),
and thus hiding information that could potentially be important.

Regards,
Bill K.