SQLite: the 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 contains 26 columns and 200,000,000 rows.

SELECT COUNT(*) FROM ( SELECT * FROM table LIMIT 100001 ) 

took 0.27 seconds.

The next three were 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 the index to respond to the request. This at least limits the index scan to the first 10,0001 rows. If the table has 1 m rows, this saves 90% of the work.

(Using SQL Server syntax here, please translate because I cannot do this).

Instead of an identifier, you can select some indexed column. I do not know if the optimizer can do this myself.

Note that smart tricks, such as looking at the values ​​of an identifier or column identifier, do not work at all.

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 part of the column is important, you do not want to do something like counting *, you want to be as specific as possible, forcing SQL to use the index that exists for each row in this table.

Answer 3

SELECT COUNT will bite you when you have many lines. It works, but you request the fastest way in sqlite.

Using:

SELECT max(rowid) AS rowcount

As NoLifeKing noted, this works correctly if you have no exceptions. Otherwise, it will be incompatible, and you will have a higher set of rows, as in the table.

Check out http://www.sqlite.org/autoinc.html for rules on how Sqlite creates rowid.

Answer 4

If you don't have an indexed column, I think the fastest way would be (for N = 100000)

 select 1 from test1 limit 1 offset 100000; 

This is because you only get N + 1 rows. No counter, no more than max.

you can try it here sqlfiddle

Answer 5

 SELECT COUNT (Column) FROM TABLE 

Make sure the selected column is indexed, preferably INT.

Answer 6

 SELECT CASE WHEN COUNT(indexed_column) >= 100000 THEN 1 ELSE 0 END FROM table 

Shouldn't it be? Reports 1 if it exceeds 100,000 and 0 otherwise

Answer 7

 select count(*) from table select max(id) from table 

if the table has a primary key identifier, the second -

Answer 8

 select count(*) from table 

- The query optimizer automatically selects an indexed column.