CODEWITHSUNDEEP
sql
ria
ria

Reputation: 7984

how does sql count work?

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.

Upvotes: 22

Views: 10780

Answers (8)

Fraga
Fraga

Reputation: 1451

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

Upvotes: -1

analogue
analogue

Reputation: 3190

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.

Upvotes: 1

p.marino
p.marino

Reputation: 6252

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.

Upvotes: 3

pcent
pcent

Reputation: 2049

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

Edit: See this link

Upvotes: 0

psmears
psmears

Reputation: 28040

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.

Upvotes: 7

MarkR
MarkR

Reputation: 63558

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.

Upvotes: 14

Marcelo Cantos
Marcelo Cantos

Reputation: 185902

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.

Upvotes: 2

Pranay Rana
Pranay Rana

Reputation: 176926

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

Upvotes: 1

Related Questions