I have table User and table Order, now I want to find the names of user who made more than 100 orders, I can do a query like below:
SELECT U.name FROM User U WHERE 100 < ( SELECT COUNT(*) FROM Orders O WHERE O.uid=U.uid )
This is slow because of correlated subquery.
Therefore I think I can optimize it by creating a view which contains how many order each user has made like below
View UserOrderCount uid orderCount 0 11 1 108 2 100 3 99 4 32 5 67
Then the query is much simpler:
SELECT U.name FROM User U, UserOrderCount C WHERE 100 < C.orderCount And U.uid=C.cid;
But this turn out to take more time, I can't figure out why... Please shed some light on this, thanks in advance!
Here is how the view is created:
CREATE VIEW UserOrderCount AS select U.uid, count(*) AS orderCount from User U, orders O group by U.uid;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `Orders` ADD INDEX `orders_idx_uid` (`uid`);
SELECT U.name FROM User U WHERE 100 < ( SELECT COUNT(*) FROM Orders O WHERE O.uid = U.uid )