I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it would solve my problem. Bayuah provided this answer:
SELECT table_rows "Rows Count" FROM information_schema.tables WHERE table_name="Table_Name" AND table_schema="Database_Name";
Which I liked because it looks like a lookup instead of a scan, so it should be fast, but I decided to test it against
SELECT COUNT(*) FROM table
to see how much of a performance difference there was.
Unfortunately I'm getting different answers as shown below:
Why are the answers different by roughly 2 million rows? I am guessing the query that performs a full table scan is the more accurate number, but is there a way I can get the correct number without having to run this slow query?
ANALYZE TABLE data_302, which completed in 0.05 seconds. When I ran the query again, I now get a much closer result of 34384599 rows, but it's still not the same number as
select count(*) with 34906061 rows. Does analyze table return immediately and process in the background? I feel its worth mentioning this is a test database and is not currently being written to.
Nobody is going to care if it's just a case of telling someone how big a table is, but I wanted to pass the row count to a bit of code that would use that figure to create a "equally sized" asynchronous queries to query the database in parallel, similar to the method shown in Increasing slow query performance with the parallel query execution by Alexander Rubin. As it is, I will just get the highest id with
SELECT id from table_name order by id DESC limit 1 and hope my tables don't get too fragmented.