the efficiency of MYSQL COUNT query

Question

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?

Dan Grossman · Accepted Answer · 2011-08-08 06:12:17Z

4

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.

answered Aug 8, 2011 at 6:12

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Grossman Over a year ago

Then it'll probably use the primary key index. The row count is not stored separately for InnoDB tables. The EXPLAIN would tell you this. For MyISAM it'll say Select tables optimized away, for InnoDB it'll tell you what index, if any, is used.

Pelshoff · Accepted Answer · 2011-08-08 06:13:46Z

0

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.

answered Aug 8, 2011 at 6:13

Pelshoff

1,47410 silver badges14 bronze badges

Comments

rockerest · Accepted Answer · 2011-08-08 17:04:52Z

0

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.

edited Aug 8, 2011 at 17:04

answered Aug 8, 2011 at 6:13

rockerest

10.5k3 gold badges39 silver badges67 bronze badges

2 Comments

Dan Grossman Over a year ago

The page you link contradicts what you said. InnoDB is not the same way, whether there is a WHERE clause or not. I think you've misread.

rockerest Over a year ago

@Dan, you're right. It was very late when I answered this question :) I've fixed the error in my answer.

Collectives™ on Stack Overflow

the efficiency of MYSQL COUNT query

3 Answers 3

1 Comment

Comments

Edit:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Edit:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related