MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;

by

It depends on the storage engine.

  • For MyISAM the total row count is stored for each table so SELECT COUNT(*) FROM yourtable is an operation O(1). It just needs to read this value.
  • For InnoDB the total row count is not stored so a full scan is required. This is an O(n) operation.

From the manual:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 13.2.13.1, “InnoDB Performance Tuning Tips“.

More Related Contents:

  1. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  2. 1052: Column ‘id’ in field list is ambiguous
  3. Error Code: 2013. Lost connection to MySQL server during query
  4. Count the occurrences of DISTINCT values
  5. MySQL Trigger after update only if row has changed
  6. difference between primary key and unique key
  7. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  8. MySQL Query to select data from last week?
  9. Using an Alias in SQL Calculations
  10. Can a table field contain a hyphen?
  11. Adding a leading zero to some values in column in MySQL
  12. How do I add a custom CHECK constraint on a MySQL table?
  13. View’s SELECT contains a subquery in the FROM clause
  14. How to insert data to MySQL with auto-incremented column(field)?
  15. How to change the default port of mysql from 3306 to 3360
  16. Case statement in MySQL
  17. Replace Into Query Syntax
  18. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  19. Laravel join with 3 Tables
  20. Difference between two table structure
  21. MySQL query, MAX() + GROUP BY
  22. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  23. How to insert a file in MySQL database?
  24. How to get multiple records against one record based on relation?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. How to use a user variables in MySQL LIKE clause?
  28. How to speed up sql queries ? Indexes?
  29. Selecting distinct 2 columns combination in mysql
  30. MySQL: Computed Column

Leave a Comment