Contents
How to get a fast table count in MySQL?
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. If an approximate row count is sufficient, SHOW TABLE STATUS can be used.
Which is the best way to count rows in MySQL?
When you COUNT (*) it takes in count column indexes, so it will be the best result. Mysql with MyISAM engine actually stores row count, it doensn’t count all rows each time you try to count all rows. (based on primary key’s column) Using PHP to count rows is not very smart, because you have to send data from mysql to php.
How to get a fresh count in MySQL?
As of MySQL 5.1.6 you can use the Event Scheduler and insert the count to a stats table regularly. It’s not perfect but it offers a self contained solution (no cronjob or queue) that can be easily tailored to run as often as the required freshness of the count.
Is it smart to use PHP to count rows?
Using PHP to count rows is not very smart, because you have to send data from mysql to php. Why do it when you can achieve the same on the mysql side?
How many rows can I insert per day in MySQL?
A side note: 640K rows inserted per day — this approaches the limit for single-row INSERTs in MySQL with your current settings on a HDD (not SDD). If you need to discuss the potential disaster, open another Question. Run any timing query twice; use the second time.
How to get a fast count in InnoDB?
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. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.
Which is faster, MySQL or TMP table?
It may be faster than delivering the rows to the client; it will have to collect the rows internally in a tmp table, but deliver only the COUNT. A side note: 640K rows inserted per day — this approaches the limit for single-row INSERTs in MySQL with your current settings on a HDD (not SDD).