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

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

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

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 – zerkms Jun 8 at 6:28 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 metadata – Tudor Constantin Jun 8 at 7:23.

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?

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.

– Maxim Dsouza Jun 8 at 7:30 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 – Dr McKay Jun 8 at 8:15.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.