[FIXED] What is faster - count(*) OR select row + update MYSQL

Issue

I have the following situation: A user can have maximum number of partnerships. For example - 40.000

Question: In case user wants to add a new partnership, how it will be faster to check the current number of partnerships ?

Solution 1: Using a count(*) statement ?

Solution 2: Storing the value into a separate column of user. And always when a new partnership needs to be added, to get it and then to increment that column ?

Personal remarks: Are there any better solution to check the total number of rows ?

Does anyone have a statistic of how performance is affected during time ? I suppose that solution 1 is faster when there are a limited number of rows. But in case there are multiple rows, then it makes more sense to use solution 2. For example, after what period of time (amount of rows) solution 2 becomes better than 1 ?

I would prefer of course solution 1, because I get more control. Bugs might happen and the column from solution 2 to not be incremented. And in such cases, the number will not be correct.

Solution

I'll vote for Solution 2 (keep an exact count elsewhere).

This will be much faster than COUNT(*), but there are things that can go wrong. Adding/deleting a partnership implies incrementing/decrementing the counter. And is there some case that is not exactly an INSERT/DELETE?

The count should be done in a transaction. For "adding":

START TRANSACTION;
SELECT p_count FROM Users WHERE user_id = 123 FOR UPDATE;
if >= 40K and close the transaction
INSERT INTO partnerships ...;
UPDATE Users SET p_count = p_count+1 WHERE user_id = 123;
COMMIT;

The overhead that is involved might be as much as 10ms. Counting to 40K would be much slower.

Answered By - Rick James

Answer Checked By - Candace Johnson (PHPFixing Volunteer)