Query to count the number of tables in MySQL database

MySQL Query Magic

So, we often run into a case where we want to performance tune our database server. This is done usually to get faster load times on websites. That said, one thing we look into often is just how many tables are in a database being used on our server. We do this for each database we host, then count the total, and performance tune our database server for that number. This has the advantage of never over-utilizing resources, which in turn results in faster load times because of less overhead. (It's not that simple, but it's a step in the process).
We figured since we check often, it may be something others have wondered about. So we decided a quick tutorial would benefit everyone.
So, from your console, log in as the root user for the database, or a user which has permission to the database you want to get a table count on. Typically on a linux machine this is done as:

mysql -uroot -p

Or you can substitute the root user as follows:

mysql -uUser -p

Once you gain access to the server, you can get the number of tables in a database by typing the following:

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbName';

You should see something similar to:

+----------+
| COUNT(*) |
+----------+
| 154 |
+----------+
1 row in set (0.01 sec)


That means your database has 154 tables in it.

This is a super quick and easy thing you can do to get the number of tables in your database.

Tags