Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

455
Views
mysql> SELECT COUNT(*) vs SHOW TABLE STATUS for row count

We have a table in our database that has teens of millions of entries (10.1.21-MariaDB; InnoDB table engine; Windows OS). We are able to get the number of rows in the table instantaneously using the command SHOW TABLE STATUS LIKE 'my_table_name'. However, SELECT COUNT(*) FROM my_table_name takes a few minutes to complete.


Q) Why is SHOW TABLE STATUS LIKE 'my_table_name' so so much quicker than SELECT COUNT(*) FROM my_table_name?

over 4 years ago · Santiago Trujillo
2 answers
Answer question

0

Because one is a query that counts all the rows and the other is a command that retrieves stats the DB engine maintains about the table. There isn't any firm guarantee that the table statistic will be up to date so the only way to get an accurate count is to count the rows, but it might be that you don't need it to be perfectly accurate all the time. You can thus choose either, depending on your desire for accuracy vs speed etc.

See here this screenshot from https://pingcap.com/docs/stable/sql-statements/sql-statement-show-table-status/

enter image description here

You can see the example inserts 5 rows but the table stats are out of date and the table still reports 0 rows. Running ANALYZE TABLE will (probably) take longer than counting the rows, but the stats will be up to date (for a while at least) afterwards.

over 4 years ago · Santiago Trujillo Report

0

A suggested approach to get a reasonably accurate count of the table size, when SELECT COUNT(*) is taking a long time to complete, could be:
ANALYZE TABLE my_table_name; SHOW TABLE STATUS LIKE 'my_table_name';

This comes in especially handy when importing a large amount of data into a table, and you want to track the progress of the import process.

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!