0

Say if I wanted to add the functionality of logging user actions within a web application. My table schema would look similar to the following:

tbl_history:

+----+---------+--+-----------+
| id | user_id |  | action_id |
+----+---------+--+-----------+
| 1  |       1 |  |         1 |
| 1  |       1 |  |         2 |
| 1  |       2 |  |         2 |
+----+---------+--+-----------+

A user can generate many actions so I will need to paginate this history. In order to do this I will need to figure out the total amount of rows for the user then calculate how many pages of data there should be.

Which would method be the most efficient if I were to have hundreds of users generating thousands of rows of data each day?

A)

Using the MYSQL's COUNT() function to query the amount of rows of data in the tbl_history table for a particular user.

B)

Having another table which would keep a count of history for the user within the tbl_history table.

+---------+--+---------------+
| user_id |  | history_count |
+---------+--+---------------+
|       1 |  |             2 |
|       2 |  |             1 |
+---------+--+---------------+

This will allow me to instantly get the total count of rows with a simple query in less than 1ms.

The tradeoff is that I will need to perform more queries updating the count for each user and also again on page load.

Which method is more efficient to use? Or is there any other better method? Any technical explanation would be great.

Thanks in advance.

Joel Murphy
  • 2,472
  • 3
  • 29
  • 47
  • 1
    Use `count()` *until* there is such a time when it is not "sufficiently fast". At that point, examine why and then, perhaps (*after* index options have been explored), build an (*automatically* generated/maintained) set of *denormalized data*. – user2864740 Oct 30 '13 at 17:44
  • Also, for MySQL, see [SQL_CALC_FOUND_ROWS](http://stackoverflow.com/questions/5279390/get-total-for-limit-in-mysql-using-same-query) – user2864740 Oct 30 '13 at 17:49

0 Answers0