[Proposal] Optimize existsBy... queries to Use `SELECT EXISTS(...)` Instead of `SELECT COUNT(*) > 0`

[Longwater1234]

Thanks for the great work on the Spring Data JPA project! There is one potential important improvement. Currently, the Spring Data JPA generates queries like:

SELECT COUNT(*) > 0 FROM my_table WHERE some_column = ?

for repository methods like:

boolean existsBySomeColumn(String value);

While this works just fine, a more optimal SQL pattern exists and is supported by all major relational databases: using SELECT EXISTS (...). This allows the database engine to short-circuit and return as soon as a matching row is found, potentially improving performance, especially on very large datasets. (think about tables with rows magnitude of 10^x).

Another reason: many DB engines skip full index/table scans for EXISTS, but must scan all matching rows for COUNT(*). This can be proved using EXPLAIN ANALYZE or EXPLAIN for both queries.

Main proposal

Instead of generating:

SELECT COUNT(*) > 0 FROM my_table WHERE some_column = ?

Generate, using universal JPQL as usual (no need of nativeQuery):

SELECT EXISTS (SELECT 1 FROM my_table WHERE some_column = ?)

Compatibility

The SELECT EXISTS (...) form is ANSI SQL-compliant and has been supported by virtually all relational databases for decades. There is no risk of incompatibility. MySQL, Postgres, SQLite, SQLServer, Oracle and more.

Quick Example

Repository:

public interface UserRepository extends JpaRepository<User, Long> {
    boolean existsByEmail(String email);
}

Generated SQL (suggested):

SELECT EXISTS (SELECT 1 FROM users WHERE email = ?)

Backwards Compatibility Concerns ?

If there are edge cases or dialect-specific concerns, or backwards compatibility (opting-out), perhaps this could be controllable via a Spring Data JPA property (in .yml or .properties). For example:

spring.data.jpa.repository.exists-query-strategy=exists-subquery

[mp911de]

Thanks for your proposal. Spring Data expresses queries through JPQL and doesn't generate SQL queries directly. Our current setup selects the id (or composite id) properties resulting in SELECT f.id FROM Foo f WHERE f.foo = :foo

Where have you seen (i.e. which version, maybe you can provide even a reproducer) queries like SELECT COUNT(*) > 0 as we typically do not have such an arrangement?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[Longwater1234]

@mp911de Ok, i have verified again, you are right. By default JPA does generate SELECT foo.id FROM tableName WHERE {condition}. I was basing this issue from usage of JPA Buddy to generate JPQL inside @ Query(). My proposal should instead be directed to JPA Buddy project, (a plugin for IntelliJ for Spring Data) which is the one actually generating this code of SELECT COUNT(*) > 0. THIS ISSUE CAN BE CLOSED. But you may also consider using EXISTS () instead of SELECT by id in Spring JPA.

[mp911de]

Thanks for letting us know.