Row count with PDO
Master System Design with Codemia
Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.
Introduction
Counting rows with PDO is a common source of confusion because rowCount is reliable for write statements but not consistently reliable for SELECT across drivers. For accurate query result counts, SQL COUNT(*) is usually the right approach. Clear separation between affected-row counts and result-row counts prevents subtle pagination and reporting bugs.
When rowCount Is Reliable
For INSERT, UPDATE, and DELETE, rowCount generally reports affected rows.
This is the intended and portable use case.
Why rowCount for SELECT Is Problematic
For SELECT, many PDO drivers do not guarantee consistent rowCount behavior. Depending on driver and buffering mode, result may be zero, unknown, or inconsistent.
Use explicit counting SQL instead.
This is reliable and communicates intent clearly.
Pagination Pattern: Count Query Plus Data Query
For paginated endpoints, run two coordinated queries.
This scales better than fetching full result sets only to call count in PHP.
Counting Joined and Filtered Results Correctly
Your COUNT(*) query must mirror filtering logic from data query.
If count and data filters diverge, pagination totals become incorrect.
Memory and Performance Considerations
Fetching all rows just to count them is expensive for large datasets.
This is acceptable only for small result sets where rows are already needed in memory.
For large data, push counting into SQL engine where indexes and query planner can optimize.
Transaction Use Cases
In transactional workflows, affected-row counts are useful for operational checks.
This provides useful metrics and sanity checks during maintenance jobs.
Error Handling and Driver Consistency
Always enable exception mode so query problems are visible.
Also document driver assumptions in code comments, especially if your project might move between MySQL, PostgreSQL, and SQLite.
Avoid Deprecated Counting Shortcuts
Some legacy MySQL examples use SQL_CALC_FOUND_ROWS, but it is deprecated and often slower than explicit COUNT(*) queries. Keeping a separate count query is clearer, easier to optimize, and more portable across database engines.
Observability for Query Counts
Track both count-query latency and data-query latency in monitoring dashboards. Seeing these metrics separately helps identify slow count operations before pagination performance degrades for users.
Common Pitfalls
- Using
rowCountforSELECTand assuming portability. - Counting results by fetching all rows unnecessarily.
- Mismatching filters between count query and data query.
- Forgetting integer binding for pagination parameters.
- Running without exception mode and missing query errors.
Summary
- Use
rowCountprimarily for affected rows in write statements. - Use
COUNT(*)for reliable result counts onSELECT. - Keep count query and paged data query logic aligned.
- Avoid memory-heavy fetch-all counting on large datasets.
- Configure PDO error handling and driver-aware practices for stable behavior.

