question on efficient sql query

[shuklix]

Hi All,

I have 2 tables Order and OrderDetail(see below). Order has basic order information and orderDetail has more information on the order. One entry in order can have zero or more entries in OrderDetail.

I am preparing a report where I have to find. Orders that have no entry in OrderDetails table in a period of $numdays.

My query looks like:

" SELECT COUNT(distinct m1.ordID) as count from ORDER as m1 LEFT JOIN OrderDetail AS m2 ON m1.ordID=m2.ordID where TO_DAYS(NOW()) - TO_DAYS(m1.ordDate) <= $numDays AND m2.ordID IS NULL.

The query makes a LEFT JOIN between Order and OrderDetail and gets the orderID where orderDetails.orderId == NULL.
This query takes more than 20 minutes to run(it is running for last 30 minutes and has still not finished). I am using MySql 3.23.

In my database OrderTable has more t han 85,000 rows and Order details has 80,000 rows.

Is there some way I can do it more efficiently ?


Thanks,
Shuklix

Order(

orderId int, //primary key
ordDate date, //date order was placed
orderInfo string, //payment infomration
)

OrderDetail(
OrderDetailsId int, //primary key
orderDetailDate date,
orderId int,
)

 

[TonyGroves]

Try:[tt]
WHERE m1.ordDate <= DATE_SUB(NOW(), INTERVAL $numDays DAY) AND m2.ordID IS NULL
[/tt]

If there is an index on m1.ordDate, the query engine will use it, which should be much faster.


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Sorry! That should be:
[tt] WHERE m1.ordDate > DATE_SUB(NOW(), INTERVAL $numDays DAY) AND m2.ordID IS NULL[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[shuklix]

Thanks Tony,

the orderDate is not indexed.

I changed the where clause but it did not help.

My guess is that there is some problem with the LEFT JOIN. The same query (mentioned below) without the LEFT JOIN, runs with in 25 seconds.

SELECT COUNT(distinct m1.ordID) as count from ORDER as m1 , OrderDetail AS m2 where m1.ordID=m2.ordID AND TO_DAYS(NOW()) - TO_DAYS(m1.ordDate) <= $numDays.

Shuklix

 

[TonyGroves]

Have you an index on OrderDetail.orderID? If not, that would definitely be your problem. I would also recommend the new syntax for the WHERE clause, in case you do decide to index m1.ordDate sometime.

-----
ALTER world DROP injustice, ADD peace;