SQL row count query to check if there are more records than a specific limit in Jdbi

Question

In the code, I have to check whether the table contains more than 1000 rows or not. The table can be any one, so I can't use a WHERE condition on any ID or other columns.

Right now, I'm using the Jdbi package to execute queries in Java.

The main issue is that when the table contains billions of records, the

SELECT COUNT(*) FROM <tablename>

query takes a lot of time. Where I just have to check if the table has more then 1000 rows.

I also tried adding a LIMIT to the query, but it's not working, as the COUNT(*) query returns only one result, and applying a LIMIT on that won't work.

The issue is not limited to MySQL only. I'm looking for a generic answer for most of the relational databases like Postgres, MySQL, SQLServer, AWS Databricks.....

Does anyone have an idea of how we can optimize this type of query?

Answer 1

You can put a LIMIT or TOP inside a subquery

SELECT
  CASE WHEN COUNT(*) > 1000 THEN 1 END AS isGtThan1000
FROM (
    SELECT 1 AS dummy
    FROM YourTable
    LIMIT 1001
) t;