[SQL] Is there a way to IGNORE INDEX (PRIMARY) or FORCE INDEX in hql?

I have the below hql query:

@Query("SELECT es FROM EmailSubscription es "
+ "JOIN FETCH es.subscriber s "
+ "WHERE es.subscriptionTypeId = :typeId "
+ "AND es.active = :active "
+ "AND es.id > :batchStartId "
+ "ORDER BY es.id ASC")
@QueryHints(@QueryHint(name = "org.hibernate.fetchSize", value = "1000"))


Unfortunately it takes about 2+ seconds to fetch 1k records loading EmailSubscription and its relation subscriber.

This native sql query finishes under 500 ms, but since it is native sql it doesn't load its relation (subscriber).

@Query(value = "SELECT es.* FROM subscriptions.email_subscriptions es "
+ "IGNORE INDEX (PRIMARY) "
+ "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
+ "WHERE es.type_id = :typeId "
+ "AND es.active = :active "
+ "AND es.id > :batchStartId "
+ "ORDER BY es.id ", nativeQuery = true)


Is there a way I can force an index or choose to ignore primary index in my hql query?

Continue reading...