CODEWITHSUNDEEP
mysql
kamikaze_pilot
kamikaze_pilot

Reputation: 14844

the efficiency of MYSQL COUNT query

so I executed this query on a table:

EXPLAIN SELECT COUNT(*) FROM table;

and the 'rows' column from the output is displayed as NULL (whereas usually it will show how many rows the query went through)...

does this mean that the COUNT command is instantaneous and therefore does not require going through any row whatsoever?

Upvotes: 1

Views: 434

Answers (3)

rockerest
rockerest

Reputation: 10518

Many database engines use an index scan to get the count. If you're using MyISAM (fairly likely), it just reads a number in the engine index and returns it. Nearly instantaneous.

Edit:

InnoDB does a full table scan, so it will almost always be slower (than an engine that uses the table index), unless you're comparing queries with a WHERE clause.

Upvotes: 0

Pelshoff
Pelshoff

Reputation: 1464

From: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#COUNT.28.2A.29

Depending on what engine you are using, and I'm guessing it's MyISAM, a quick index count is executed rather than actually counting all the rows.

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

If your table uses the MyISAM storage engine, then yes, that query resolves in constant time. The row count is part of the table metadata, the table itself does not have to be examined.

Upvotes: 4

Related Questions