I posted this topic yesterday but I felt that I did not clarify it properly. So I'll repost the clarified one as a new post (hope you don't mind).
I'm building a large scale application and was wondering the difference between static counts vs dynamic counts.
Right now, I'm using dynamic counts. For an example:
SELECT *,
(SELECT COUNT(1) FROM CarImages WHERE carid = carid) as ImageCount,
(SELECT COUNT(1) FROM CarBuyers WHERE carid = id) as BuyerCount,
(SELECT COUNT(1) FROM CarSellers WHERE carid = id) as SellerCount,
FROM Cars WHERE CarCondition > 2 AND IsCarHasGoodRecord(carid);
Inside IsCarHasGoodRecord function:
function IsCarHasGoodRecrd(carid INT) RETURNS BOOLEAN
BEGIN
DECLARE GoodCondition INT; DECLARE CarMaxConditionId INT;
SELECT 1 INTO CarMaxConditionId FROM CarMaxConditions WHERE carid = carid;
SELECT 1 INTO GoodCondition FROM KellyBlueBookConfirm WHERE carid = carid AND carmaxid = CarMaxConditionId;
--If the current "car row" is good condition, return true so that the car will be included in the result set
if GoodCondition = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$;
What happens is that for each car row, carimage count, car buyer count, car seller count is retrieved. Then the car id is passed to the IsCarHasGoodRecrd to find out if the car has a good record and whether to include it in the result set. As you can tell from the query above, about 3 correlated subqueries are executed in the main query, and 2 other correlate subqueries are returned in the function...totaling 5 correlated subqueries for each row in car table.
Now, if everything is properly INDEXED, on a high-end server (2x dual opteron, 4gigs ram, 2x 250 gig)...with a large concurrent userbase and 20 million rows in all tables......what is performance going to be like? I never dealt with such databases or userbase so I don't know what to expect.
I was thinking of using static counts (where the there is a column for each count in the parent table) but it's too much maintenance and i would have to use transactions everytime i update it to make sure the counts are not off.