Do you have a question? Post it now! No Registration Necessary. Now with pictures!
December 19, 2005, 10:24 pm
rate this thread
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
select line_ord.row_ID, line_ord.record, customer.customer_type,
customer.company_abrv, line_ord.item, line_ord.`list`,
orders.fhdisc, orders.sstopdisc, orders.ex_rate, line_ord.date_,
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
Re: Query is really slow
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.
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.
- » putting linux & MySQL on 6 year old box - hwo much RAM, Hard drive space do I need?
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum