How fast is Hibernate and MySQL

I would like to find what is the best practice to build method like public Boolean isUser() when I use Hibernate framework.

For the test I build a simple user table contains 285 141 records.
I use simple select to find a user like

SELECT * FROM mavenweb.users where email="3733847@test.com";

They return 1 row in 218 Millisec.
They return 1 row in 234 Millisec.
They return 1 row in 219 Millisec.

After indexing email column sql query is much faster.

They return 1 row in 031 Millisec.
They return 1 row in 000 Millisec.
They return 1 row in 015 Millisec.

I removed index from email and now we can start testing isUser() in my code.

In first solution I used “find” method from org.springframework.orm.hibernate3.HibernateTemplate and use Hibernate Query Language (HQL):

@Override
    public Boolean isUser(User user) {
        List<User> result =  hibernateTemplate.find("from User where email=?",user.getEmail());
        if(result==null){
            return false;
        }
        return true;
    }

Search:3733847@test.com Time: 343 Millisec. isUser:true
Search:3733847@test.com Time: 343 Millisec. isUser:true
Search:3733847@test.com Time: 358 Millisec. isUser:true

The time it isn’t so bad and comparable to results from sql query ;-).

I change my code a little bit and now I use executeQuery method on import org.hibernate.Session and use native sql.

@Override
    public Boolean isUser(User user) {
        // TODO Auto-generated method stub
        Session session = sessionFactory.getCurrentSession();
        List<User> result =session.createSQLQuery("SELECT * FROM mavenweb.users where email='"+user.getEmail()+"'").list();
        if(result==null || result.isEmpty()){
            return false;
        }
        return true;
}
Search:3733847@test.com Time: 270 Millisec. isUser:true
Search:3733847@test.com Time: 290 Millisec. isUser:true
Search:3733847@test.com Time: 310 Millisec. isUser:true

Looks nice. Times is much better than previously so I think is better idea to use native sql in this case.

Let’s check what happen if we revert index in the user table and use HQL.

Search:3733847@test.com Time: 156 Millisec. isUser:true
Search:3733847@test.com Time: 158 Millisec. isUser:true
Search:3733847@test.com Time: 156 Millisec. isUser:true

And again let’s check executeQuery with native SQL

Search:3733847@test.com Time: 78 Millisec. isUser:true
Search:3733847@test.com Time: 70 Millisec. isUser:true
Search:3733847@test.com Time: 78 Millisec. isUser:true

So the winner is…

No.	Name	Time in milli sec	description
	SQL Query in Workbrench	224
	SQL Query in Workbrench + index on email	15
1	HQL	348
2	Native sql	290
3	HQL + index on email	157	2 time quicker than method 1
4	Native sql + index on email	75	4 time quicker then method 2