Is COUNT faster than pulling the records and counting in code?
mysql, sql
Solution
If you know you need the data, go ahead and pull it and count it in code. However, if you only need the count, it is significantly faster to pull the count from the database than it is to actually retrieve rows. Also it is standard practice to only pull what you need.
For instance, if you are counting all the rows in a table, most database implementations do not need to look at any rows. Tables know how many rows they have. If the query has filters in the `where` clause and it can use an index, it again will not need to look at the actual rows' data, just counts the rows from the index.
And all this is not counting the less data transferred.
A rule of thumb about database speeds is go ahead and try it for yourself. General rules are not always a good indicator. For instance, if the table was 10 rows and only a few columns, I might just pull the whole thing anyway on the off chance I needed it, since 2 round trips to the database would outweigh the cost of the query.
Problem
Here is the situation: I first need to run a query to know how many records exist. For example: `SELECT COUNT(DISTINCT userid) from users;` Often this will be all that's needed. However, sometimes (say 30% of the time) following the first query, the user will want to run a second query, detailing the records. For example: `SELECT * FROM users;` Is there any reason to run `SELECT COUNT` initially instead of just `SELECT`? That is, is making the count of records in SQL faster than actually pulling the records back? Or is it doing essentially the same work either way and so I should avoid doing two queries? In other words, is it better to just always pull the records in the first query (not use `COUNT`), then count the records in code (Java). If the user wants to run the second query, then great, I already have the data. If not, then just dump it. What's the best practice here?