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

Installing FFmpeg on CentOS without a lot of trouble From time to time, I have to fire up my good... Read more
Video creation 101 The video is an electronic method for recording, playback, copying, broadcasting, and display of visuals. It was... Read more
Video Marketing in the Modern World Building a well informing video takes time, talent and passion. It is time-consuming and... 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

Recent Tweets

Socialize with us


Go to top