How Can I Efficiently Count Rows in a Very Large Database Table?

Accurate statistical method for row count in large database tables

For huge database tables with massive rows and columns, accurately counting rows can be very time-consuming. The "SELECT COUNT(*)" query is usually significantly less efficient in this case.

This article provides a database vendor-independent solution:

Database vendor independent solution:

• Use standard "COUNT(*)".

Although some database vendors (such as SQL Server) may have other solutions that claim to be able to approximate the number of rows, these methods are beyond the scope of the vendor-neutral methods discussed in this article.

Supplementary instructions (SQL Server example):

For a table with 1.4 billion rows and 12 columns in a SQL Server database:

• "SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)" took 5 minutes and 46 seconds to count 1,401,659,700 rows.
• "SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'MyBigtable' AND (index_id..." (Full SQL Server sample code omitted here)

The above is the detailed content of How Can I Efficiently Count Rows in a Very Large Database Table?. For more information, please follow other related articles on the PHP Chinese website!