What is the fastest way to count the exact number of rows in a very large table?

Question

I have come across articles that state that `SELECT COUNT(*) FROM TABLE_NAME` will be slow when the table has lots of rows and lots of columns. I have a table that might contain even billions of rows [it has approximately 15 columns]. Is there a better way to get the **EXACT** count of the number of rows of a table? Please consider the following before your answer: - I am looking for a database vendor independent solution. It is OK if it covers **MySQL**, **Oracle**, **MS SQL Server**. But if there is **really** no database vendor independent solution then I will settle for different solutions for different database vendors. - I cannot use any other external tool to do this. I am mainly looking for a SQL based solution. - I cannot normalize my database design any further. It is already in 3NF and moreover a lot of code has already been written around it.

Answer 1

Hi, Kya - there are times when optimal efficiency simply doesn't matter, but you are correct - in general, you don't want to be passing (*) into COUNT to produce a record count (open or qualified).

Instead, you want to select a single field - a primary key is a good choice.

All/most tables that I've designed contain a primary key field called ID ... so I would execute a count query that looks something like this:

SELECT COUNT(id) FROM TABLE_NAME .... or

SELECT COUNT(id) FROM TABLE_NAME WHERE <some condition >

Yes, this would exec much much faster over a table that contains 100s M or Bs of records ... and BTW, the same approach applies to all RDBMs.

So you might want to become familiar with your source table's schema before you go executing a COUNT() query ... what fields make up that table? Is there a primary key? Likely ... so what's that field's name?

Good luck!

--Lou