Reputation: 1649
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?
Upvotes: 1
Views: 306
Reputation: 72229
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;
Upvotes: 1