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

Video Marketing for Business: Here is what the experts are saying If you are an entrepreneur in this era, you... Read more
Brand Yourself for Web Marketing It's time to talk about the feature that will set you apart from your competition... Read more
Time to Make your Presence on the Web Felt: How Much you Should invest? You know what you want to... 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

  • This needs to end... Senseless violence all for what? https://t.co/mNMP0uMnJN 1 week 6 days ago
  • RT : My warmest condolences and sympathies to the victims and families of the terrible Las Vegas shooting. God bless you! 1 week 6 days ago
  • RT : Michelle & I are praying for the victims in Las Vegas. Our thoughts are with their families & everyone enduring another senseless tragedy. 1 week 6 days ago
  • RT : Las Vegas update: - At least 400 wounded taken to hospitals, per police - Gunman found dead in hotel room https://t.co/5EOndn2tkj 1 week 6 days ago
  • RT : Keep us in your thoughts - its a very difficult and tragic evening for a lot of people ,… https://t.co/mf7x29rD9Y 1 week 6 days ago

Socialize with us


Go to top