Re: resultset rows performance

[Rick James]

Simple question; complicated answer...

* SELECT COUNT(*) FROM tbl; -- instantaneous for MyISAM tables. The row count is "dead reckoned". But only for this simple case.
* SELECT COUNT(*) FROM tbl -- for InnoDB, this picks a small secondary index and does a full scan of that index.
* InnoDB cannot be sped up without violating the "transaction isolation level" chosen.
* SELECT COUNT(*) FROM tbl WHERE ...; -- This requires more work because it has to check the rows against the WHERE clause. If there is a useful INDEX, it could be rather fast.
* SELECT SQL_CALC_FOUND_ROWS ... LIMIT ... -- does (effectively) two things: The full query, plus SELECT COUNT(*) FROM tbl WHERE ...

I am suspicious of your timings (and hence some of your conclusions) for two reasons:
* Was the "Query cache" on? (Use SELECT SQL_NO_CACHE ... to avoid it for benchmarking.)
* The first time you run a query, it may read the disk a lot; the second time you run it (or a similar query that needs the same disk blocks), it will run much faster. (Rule of Thumb: 10 times as fast.)

So...
* Avoid the QC.
* Run each test twice; use the second timing.