Optimizing Hibernate session.createQuery().list();

Question

We have a Users table (MySQL) with 120,000 rows

List<User> users = session.createQuery("from User").list();

This hibernate query takes about 6 to 9 seconds to execute. How can we optimize this? Is MySQL the bottleneck? Or is .list() usually this slow?

Answer 1

Ofcourse it's slow because the query perform the full table scan. You should join other objects associated with it, including where clause of the query, the query could be changed to return the limited number of records or use criteria API and projection.