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,
)
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,
)