SQLite: fastest way to check if a table has more than x rows

Question

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

Answer 1

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.

Answer 2

SELECT COUNT(indexed_column) FROM TableName

Now the indexed column part is the important thing, you don't want to do something like counting *, you want to be as specific as possible forcing SQL to use an index that is existing for each row on that table.