SQLite: fastest way to check if a table has more than x rows
sql, sqlite
Solution
select count(*) from (
select top 100001 ID from T
) x
We need to scan an index to answer the query. This at least restricts the index scan to the first 100001 rows. If the table has 1m rows, this saves 90% of the work.
(Using SQL Server syntax here - please translate yourself because I can't do it).
Instead of ID you can choose some indexed column. I don't know if the optimizer can do that itself.
Note, that clever tricks like looking at identity or ID column values do not work in general.
To support the above query, create a dummy column of type `bit` and index it. The index will be very compact and the fastest to scan.
Problem
What is the fastest way to check if a SQLite table has more than 100,000 rows? The test table has 26 columns and 200,000,000 rows. ``` SELECT COUNT(*) FROM ( SELECT * FROM table LIMIT 100001 ) ``` took 0.27 seconds. The following three needed 12 and a half minutes ``` SELECT COUNT(*) FROM table SELECT COUNT(*) FROM table LIMIT 100001 SELECT CASE WHEN COUNT(Id) >= 100000 THEN 1 ELSE 0 END FROM table ```