I took a look at my old accounting system, and it seems that performance is taking a role in the daily labor of the employees using it. So, I discovered that using a subquery was the problem, I’ve been reading, testing and, it seems that using a JOIN is like 100x faster as the data that we have in our Databases is huge now.
How do I can convert this subquery into a JOIN?
I’m seeking for help because I’m trying, but I’m being unable to do it, and I’m starting to think that this is not possible.
$ sql = "SELECT orders.order_id, orders.order_time, orders.order_user, orders.payment_state, orders.order_state, orders.area_name, (SELECT COUNT(*) FROM order_item WHERE order_item.order_id = orders.order_id) AS items_number FROM orders WHERE orders.order_state = 1 AND order_time BETWEEN DATE_SUB(NOW(), INTERVAL 365 DAY) AND NOW()";
Being specific, the data we are retrieving here is all the rows created in the last year from the orders table AND the number of items purchased in each order, which is called from the subquery as items_number from order_item table WHERE order_id is equal in each table.