Get a rough estimation of how many rows are in a table, without using count

When you want to know how many rows a table in MySQL has, the easiest way is:

SELECT COUNT(1) FROM <table_name>;

However, if the table is massive, this might take a long time, as it needs to look at all rows. Thankfully there is another way in mysql, where you can get a rough estimation of the row numbers:

SELECT TABLE_ROWS 
FROM information_schema.tables 
WHERE TABLE_NAME = '<table_name>'
-- Optional if you have mutliple databases:
AND TABLE_SCHEMA = '<database_name>';

This give you a rough estimation, that may be off by 30-50%, so it’s mostly useful to get an indication of whether you’re dealing with a thousand, a million or a billion rows in a table.

If you want to get notified of the next blog post, join the newsletter.