D1: What's the better practice for querying the row COUNT of my table?

[Bylx666]

Proposed changes

What I learned at class is to SELECT seq FROM sqlite_sequence or COUNT(*).

However I got no such table: sqlite_sequence: SQLITE_ERROR, and worried about the performance of using COUNT.

Is there a better way?

Subject Matter

Better practices to count rows for your table with D1.

Content Location

https://developers.cloudflare.com/d1/best-practices/query-d1/

Additional information

I get great impression about Workers. Thanks for every worker's patience.

[Oxyjun]

Hi @Bylx666 ,

The sqlite_sequence table is created after you create a table with AUTOINCREMENT and insert something into it.

• It may be better to use count(*) or count(1), unless all of your tables are using AUTOINCREMENT. But using AUTOINCREMENT on all tables could be a premature optimization.
• If you are using AUTOINCREMENT for all tables and have at least 1 row in all of the tables, then you could use SELECT seq FROM sqlite_sequence. We recommend using this only if you see performance issues and can fully confirm your tables have at least 1 row and are using AUTOINCREMENT - if you use it on tables not using AUTOINCREMENT, or without any rows, then it's going to error.

Hope this helps!

Thanks to @lambrospetrou for looking into this question.