Using setMaxResults(1) before uniqueResult() for hibernate optimization?

Question

Is writing

session.createCriteria(Person.class) .add(Restrictions.eq("id", personId)) .setMaxResults(1) .uniqueResult();

better than writing

session.createCriteria(Person.class) .add(Restrictions.eq("id", personId)) .uniqueResult();

from an optimization point of view? Will the first query be faster?

Answer 1

Sometimes explicitly limiting the result set rows to the expected number may give a hint to the database to build a more optimized query execution plan.

However, in most databases querying by primary key is the most optimal filter condition anyway so any additional conditions will bring no benefit. Actually, the additional sql fragment will just increase the statement parsing time and time spent by the db optimizer discarding the redundant filter conditions.