Databases
StackOverflow2013
Postsdbo
POWhich is more efficient? Count() or a reference to a count in another table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich is more efficient? Count() or a reference to a count in another table?
    text
    copied!<p>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:</p> <p><code>tbl_history</code>:</p> <pre><code>+----+---------+--+-----------+ | id | user_id | | action_id | +----+---------+--+-----------+ | 1 | 1 | | 1 | | 1 | 1 | | 2 | | 1 | 2 | | 2 | +----+---------+--+-----------+ </code></pre> <p>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.</p> <p>Which would method be the most efficient if I were to have hundreds of users generating thousands of rows of data each day?</p> <p><strong>A)</strong></p> <p>Using the MYSQL's <code>COUNT()</code> function to query the amount of rows of data in the <code>tbl_history</code> table for a particular user.</p> <p><strong>B)</strong></p> <p>Having another table which would keep a count of history for the user within the <code>tbl_history</code> table.</p> <pre><code>+---------+--+---------------+ | user_id | | history_count | +---------+--+---------------+ | 1 | | 2 | | 2 | | 1 | +---------+--+---------------+ </code></pre> <p>This will allow me to instantly get the total count of rows with a simple query in less than 1ms. </p> <p>The tradeoff is that I will need to perform more queries updating the count for each user and also again on page load.</p> <p>Which method is more efficient to use? Or is there any other better method? Any technical explanation would be great.</p> <p>Thanks in advance.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload