COUNT(*) vs COUNT(column_name)
A case study comparing COUNT(*) with COUNT(column_name) performance in SQLite.
Parameters
| Setting | Value |
|---|---|
| Database | SQLite 3.50.4 (64-bit) |
| OS | Arch Linux x86_64 |
| CPU | AMD Ryzen 5 6600H |
| Table Rows | 100,000 |
Experiment
- Create a database with random values (100,000 rows) using Python
- Run query 30 times to avoid cold start bias
Results
The chart compares query execution times between COUNT(*) and COUNT(column_name):
Conclusion
In places where NULL values aren't an issue, COUNT(*) is the preferred counting method for efficiency in SQLite.
💡 Key Takeaway: Use COUNT() when you want the total count of rows regardless of NULL values. Use COUNT(column_name) only when you need to count only non-NULL values in that specific column. For maximum efficiency in SQLite, prefer COUNT().