MySQL InnoDB count(*) vs counting rows on server side

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?

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.).

I 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.

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?