The most efficient way to count query strings

Question

I use Hibernate to retrieve the number of rows for a particular request. Let's say I have a table called "Man" with various columns. One of these columns is "name".

If I wanted to get the number of people named "Andrey", which of these methods would be the most effective? 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 only 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 a Request

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

(4) Using Count with a name column in a 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 numbers 3 on my list

Answer 1

Do not retrieve the result set if you just want to count the number of rows, it just means useless overhead:

• You’ll get more materials than you want (regardless of whether you select all columns or just one).
• you will need to send them by cable
• you need to instantiate (whether it's a complete Person object or just a String ) for nothing.

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

This excludes (1) and (2).

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

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

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

I would use (3).

Related questions

• COUNT (*) versus COUNT (1) and COUNT (pk): which is better?
• count (*) vs count (column-name) - or rather?
• Count (*) vs Count (1)
• SQL Query: which one should I use? count ("columnname"), count (1)

Answer 2

The count(*) method is profiled much faster than the size() method for my company. This is certainly more efficient in terms of memory, since you are not dealing with column data that you will not use. I don't know if count(name) matters.

Answer 3

The less you put inside the COUNT() function, the better. If you do not need any information from the table, I would say use COUNT(1) . You can use COUNT(name) or COUNT(*) if your tables are correctly indexed.