Fastest way to count exact number of rows in a very large table?
The fastest way by far on MySQL is:
SHOW TABLE STATUS;
You will instantly get all your tables with the row count (which is the total) along with plenty of extra information if you want.
I got this script from another StackOverflow question/answer:
SELECT SUM(p.rows) FROM sys.partitions AS p
INNER JOIN sys.tables AS t
ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON s.[schema_id] = t.[schema_id]
WHERE t.name = N'YourTableNameHere'
AND s.name = N'dbo'
AND p.index_id IN (0,1);
My table has 500 million records and the above returns in less than 1ms. Meanwhile,
SELECT COUNT(id) FROM MyTable
takes a full 39 minutes, 52 seconds!
They yield the exact same number of rows (in my case, exactly 519326012).
I do not know if that would always be the case.
Simple answer:
- Database vendor independent solution = use the standard =
COUNT(*)
- There are approximate SQL Server solutions but don't use COUNT(*) = out of scope
Notes:
COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case
Edit:
SQL Server example (1.4 billion rows, 12 columns)
SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less
1 runs, 5:46 minutes, count = 1,401,659,700
--Note, sp_spaceused uses this DMV
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'MyBigtable' AND (index_id < 2)
2 runs, both under 1 second, count = 1,401,659,670
The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)