I have a MySQL InnoDB table. I need to count the number of rows in the table, but I have read in many performance tuning books that count() does not execute very quickly. Would it be faster to count the number of rows using select count() or by using Select Primary_key_column and counting the rows on server side? Or is there any other alternative to count the number of rows which does not cause a performance issue in MySql InnoDB?
Add a comment
|
3 Answers
in the case of COUNT(*) of an entire table MyISAM
is very fast because it keeps a table row count internally.
In the case of COUNT(*) ... WHERE ...
both MyISAM and InnoDB have to really count the matching
rows. Then the speed depends mainly on the amount of disk
i/o needed to access the rows.
MyISAM keeps an internal cache of table meta-data like the number of rows. This means that, generally, COUNT(*) incurs no additional cost for a well-structured query. InnoDB, however, has no such cache. For a concrete example, let’s say we’re trying to paginate a query. If you have a query SELECT * FROM users LIMIT 5,10, let’s say, running SELECT COUNT(*) FROM users LIMIT 5,10 is essentially free with MyISAM but takes the same amount of time as the first query with InnoDB. MySQL has a SQL_CALC_FOUND_ROWS option which tells InnoDB to calculate the number of rows as it runs the query, which can then be retreived by executing SELECT FOUND_ROWS(). This is very MySQL-specific, but can be necessary in certain situations, particularly if you use InnoDB for its other features (e.g., row-level locking, stored procedures, etc.).
2 Comments
zerkms
"Then the speed depends mainly on the amount of disk i/o needed to access the rows" --- this is not really true. Very often mysql don't need to access the data and perform disk operations, since there is indexes in the memory. Also
SQL_CALC_FOUND_ROWS works regardless storage engine
Tudor Constantin
You are right with disk i/o - the idea is that the time needed to return the result is dependent on time to compute the resultset size (read and count), whereas when no
where clause is present, with MyISAM - the operation is less costly - just read the metadataI speak in general - if you use count on client side it should be the same as if you run it on server side. This is because the sql statement is sent from the clinet (or server) to the database server and it performs the operation at serverside always.
However it is a good practice to use index to count subsets of all data, but if you are counting all the data in the table the indexes will be obsolete since the db has to count ALL the rows.
I don't know if there is runstats utility in mysql. In DB2 for example the statistics also tell the number of records in a table..and once runstats is performed the count is much much faster.
Comments
SELECT COUNT(*) FROM table is always faster than counting records "by hand" (like fetching only primary key column and counting length of array in, say, PHP).
In what book did you read it?
3 Comments
Maxim Dsouza
I never said I read that this was faster. I only said I read that count * executes slowly. I was only asking if that was a possible option.
Dr McKay
Not really. Each sane engine saves number of rows somewhere, so lookup is really fast. Only case it may be slow I can imagine is described here: mysqlperformanceblog.com/2007/04/10/count-vs-countcol
jave.web
@DrMcKay For simple count all rows from N tables, of course, but what if you have a query that is more complex and more variable? (The number of rows is not stored anywhere in this case)