I have several dozen tables in my database. They all have the same schema, and I needed to write a query that would give me the amounts of rows per table. Simply two columns: table name and amount of rows in it.
As mentioned here, you can do it via a pretty simple query
Two problems here though:
- It is an approximation (no big deal for me)
- No way to filter it by a certain column
The latter became important for me at some point.
Constructing and executing the query
Here is the most basic example, taken from here. It simply counts rows in each table and it is probably slower than the one above. Yet you can expand it however you want.
See the empty string at the end of the inner
CONCAT? You can put the
WHERE in there to filter your statements. Or you could get a maximum or average value instead of counting.
Also, notice the
group_concat_max_len variable. If you have many tables,
GROUP_CONCAT will trim your string to this many characters. By default it is 1024, which is a restrictively small number. So you can increase it on a per-session basis as shown above to bypass this limit. That is why I also added
SELECT @resultQuery, just to make sure nothing is trimmed before I run the query.
Wrapping into a procedure
I’m not a big fan of stored procedures. But as long as no critical business logic is built on them (been there. Ouch…), they are fine. We can easily wrap the code above into a procedure
And then, all you need to do is
CALL all_counts();. One caveat: if the amount of your tables grows rapidly, don’t forget to increase
group_concat_max_len. You may need to rerun the above query, it will replace the procedure for you.
Performing a query over an arbitrary amount of tables in a MySQL database can be a little bit unobvious. Even cumbersome. Consider this article a cheatsheet for such situations.
Thanks for tuning in!