Most efficient way to count rows of a query

Question

I'm using Hibernate to retrieve the number of rows for a specific query. Let's say I have a table called 'Person' with various columns. One of those columns is 'name'.

If I wanted to get the number of people with the name of 'Andrew', which of these ways would be most efficient? Assuming there is a performance difference between some/all of them. Is there a better way to do this using Hibernate/SQL?

(1) Select all columns

Query query = session.createQuery("from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();

(2) Select just the name column

Query query = session.createQuery("select name from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();

(3) Using Count in the query

Query query = session.createQuery("select count(*) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();

(4) Using Count with the name column in the query

Query query = session.createQuery("select count(name) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();

Edit: Sorry, I had two number 3's in my list

Answer 1

Don't retrieve a result set if you just want to count the number of rows, this just means useless overhead:

• you'll get more stuff than actually wanted (whether you're selecting all columns or just one)
• you'll need to send them over the wire
• you'll need to create instances (whether it's a full Person entity or just a String) for nothing.

In other words, if you only want to count, don't do it on the Java side, DBMS are optimized for this task and will do a much better job.

This excludes (1) and (2).

Regarding (3) and (4), note that there is a difference between count(*) and count(col) in general:

• count(*) counts ALL rows
• count(col) counts rows with non-null values of col

So they will give different results in performance and query result if col can be NULL (the count(*) being faster), otherwise identical performance.

I'd use (3).

Similar questions

• COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
• count(*) vs count(column-name) - which is more correct?
• Count(*) vs Count(1)
• SQL Query: Which one should i use? count(“columnname”), count(1)