how does sql count work?

Question

I would like to understand how exactly does sql count work. Is it a whole table scan that happens or is it some property of the table that is read. However I feel a table scan would be an overhead in case of huge tables with lots of records.

Answer 1

In general either a table or index scan is performed. This is chiefly because in a MVCC-supporting engine, different transactions could see different rows, so there is no single "row count" which is simultaneously correct for everyone.

Likewise, if you have a WHERE clause, then the where condition could be different for different clients, so they see different numbers.

If you need to do a lot of counts of large tables, consider storing your own counters in a different table. Exactly how you do this is entirely application specific.

Answer 2

This will depend very much on which SQL implementation you are using (MS SQL Server, MySQL, Oracle, PostgreSQL etc), and how clever its optimiser is.

It may also depend on the query. For example, with something like

SELECT COUNT(primary_key) FROM table;

the optimiser may realise that there is no need to scan the table (since there is no filtering with WHERE and no possibility that any values are NULL) and just return the size of the table. With a more complicated query (where there is filtering, or the possibility of NULLs), the database may have to scan the table, or it may be able to do some optimisation with the use of an index.

Answer 3

This is obviously implementation dependant (i.e. different RDBMS may employ different strategies) and usage dependant (i.e. select count(*) from mytable and select count(*) from mytable where myfield < somevalue) may use different methods even in the same DB.

If you are trying to get the count based on some partitioning that is already expressed by an Index, smart DBs will try to use the index alone. Or something like the old "rushmore" used in Foxbase.

So, "it depends", but at the end of the day, if no better methods are available, yes, the DB will perform a table scan.

Answer 4

It is usually some sort of index scan, unless there is no unique index on the table.

Strangely enough, most database engines can only count by scanning. They even provide alternate solutions to count using table metadata. For instance SQL Server supports SELECT rowcnt FROM sysindexes .... However, these are usually not 100% accurate.

Answer 5

YSE COUNT FUNCTION DOSE TABLE SCAN, rather than using count on table to get total number of row you can use :

SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'TABLENAME' 

or

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U' and sysobjects.[name]='tablename'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC

OTHER WAY TO GET TOTAL COUNT : http://www.codeproject.com/Tips/58796/Number-of-different-way-to-get-total-no-of-row-fro.aspx

Answer 6

It depends on the DBMS used.

If there is an index, there should be one index row for each table row. A smart DBMS will likely choose the smallest index and count the index rows.

Finally, if the table is small enough, it may count the table rows and bypass the index.

Answer 7

In postgreSQL a table scan is performed. I think it's implementation dependant.

Edit: See this link

Answer 8

It really doesn't matter!

I assume you want the row count for some sort of paging... so just make sure your paging algorithm is into the best practices and forget about how the engine works.

Let people in database business care about this, just follow the recommendation of those who are experts in the database your are using.

SQL Server - https://web.archive.org/web/20211020131201/https://www.4guysfromrolla.com/webtech/042606-1.shtml

Oracle - Paging with Oracle

MySQL - http://php.about.com/od/phpwithmysql/ss/php_pagination.htm