Geek Answers Handbook

MySQL - complexity: SELECT COUNT (*) FROM MyTable;

What is the complexity of this MySQL query

SELECT COUNT(*) FROM MyTable;

Is the count of the number of records in a table stored somewhere and updated every time a row is inserted or deleted? If so, then the complexity should be O (1).

+7
source share
3 answers

It depends on the storage mechanism.

  • For MyISAM, the total number of rows is stored for each table, so SELECT COUNT(*) FROM yourtable is an O (1) operation. Just need to read this value.
  • For InnoDB, the total number of rows is not saved, so a full scan is required. This is an O (n) operation.

From the manual:

InnoDB does not save the internal row count in the table. (In practice, this would be somewhat complicated due to multi-version programming.) To process the SELECT COUNT(*) FROM t , InnoDB must scan the table index, which takes some time if the index is not completely in the buffer pool. If your table does not change frequently, using the MySQL query cache is a good solution. To get a quick score, you need to use the counter table that you create, and let your application update it according to the inserts and delete it. SHOW TABLE STATUS can also be used if the approximate number of rows is sufficient. See Section 13.2.13.1, “ InnoDB Performance Tuning Tips ”.

+8
source

AFAIK in the array of strings MyISAM is cached, there isn’t any in InnoDB, and with every account he counts all the strings.

+1
source

I am not sure if this value is saved or not, but it is not at all important for your request. Using MySQL with your query, it will read all returned rows at the time of its execution.

-3
source

More articles:


All Articles