Query to count the number of tables in MySQL database

Blaine's picture
We want to performance tune our MySQL database server, and need to count the number of tables. This query will show you how in the MySQL CLI.

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. Quick and easy.

Recent Posts

Have you ever wanted to disable offloading of all your Xen DomU's? How about doing it without having to think... Read more
Video Website Themes It is essential to know how big the video market is. You have a well-operating platform with... Read more
How to create a marketing video that will stand above the rest The business world seems to be advancing every... Read more

Need Support?

Every video, every website, every chatbot, every client - 100% custom products and solutions to make your business run successfully

145 Pine Haven Shores Road #1205 Shelburne, VT

Socialize with us


Go to top