intro
Developers and database administrators have a lot of things to deal with — from building an application to keeping an eye on database uptime, availability, capacity, and/or performance. The last thing you want to work on is counting the rows in MySQL — read this blog and learn how to do that with ease.
Are you a MySQL database administrator? If the answer to this question is yes, you already perfectly know what goes into maintaining a database. Are you a developer? You know that taking care of an application can necessitate some work with your database too. Working with your database is not the easiest of tasks — proper database administration can solve a whole host of issues, but only after you’re aware of small things that when used improperly, can make your experience with the database turn into hell. Learn how to use a counter in MySQL!
Counting in MySQL
So, how exactly do you count in MySQL? There are a couple of ways. First, you can observe the count of rows in various SQL clients including phpMyAdmin if you hover over a table or open it in a new tab like so (alternatively, you can also run a SELECT * query in a table of your choice — the result will vary across numerous SQL clients, but phpMyAdmin will return it as shown):
Other SQL clients, such as the top-rated SQL client built by DbVisualizer, will also be quick to let you in on the details of a specific table if you click on the table below your database on the left-hand side like so:
For others, everything isn’t so simple. Counting hundreds of thousands or even millions of rows without automating the process is close to impossible, but as your data grows, knowing the exact row count in a table will likely become more important than ever. Sounds like a paradox, right? How do you know the exact row count in a table if you don’t use an SQL client? Is it even possible? Do you need to create a counter in MySQL?
Truth be told, a counter already exists in MySQL — enter the COUNT function in MySQL. This function counts the number of rows in a table or a column, but beware — it is not without its quirks.
COUNT as a Counter in MySQL
Many MySQL DBAs are aware of the COUNT(*) function. Indeed, this function is relatively easy to use and is able to produce results rather quickly. Let’s try it in its simplest form — SELECT COUNT(*) FROM ourtable (where ourtable is the name of our table, of course.) The results will be as follows:
Our table has 985,920 records. Cool. How does this counter in MySQL work, though? Everything’s more simple than you can imagine (sometimes):
The COUNT(*) query as a counter in MySQL isn’t only efficient when counting all of the rows in a table — it can also be used when counting the rows existing in a column, too (you guessed it — by replacing the * with a column name like so):
1
SELECT COUNT(column) FROM table;
As you can notice from the example above, the COUNT([column|*]) function can also come with the AS clause helping you define the column name that will return the results meaning that if you run a query like SELECT COUNT(col) AS counter_in_mysql FROM demo_table MySQL will return the number of rows in the col column with the name of counter_in_mysql.
COUNT in InnoDB vs MyISAM
That sounds like fun and roses, right? Well, not so fast...
Most of you will be running the default storage engine offered by MySQL — InnoDB — and here’s where you may be running into some problems. As InnoDB doesn’t store the table row count internally (the same can’t be said about MyISAM), it has to count the rows and return the result because it simply cannot make use of the metadata in a table.
COUNT(*|col) queries on tables running the MyISAM storage engine are likely to be significantly faster because when MyISAM is in use, MySQL can return the row count from the metadata itself.
A query that makes all of your MyISAM-based tables run InnoDB is as follows:
1
ALTER TABLE [table_name] ENGINE = InnoDB;
Note: Replace table_name with the names of your tables that run MyISAM. Preferably, run this statement in a loop to begin with.
Of course, you may also want to find out what tables run MyISAM in the first place before running the aforementioned query, and that can be done like so:
1
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND ENGINE = 'MyISAM';
Don’t Switch to MyISAM
At this point, some of you may consider a switch to MyISAM — not so fast!
MyISAM does store the internal row count of tables inside of itself and InnoDB doesn't, but that does not mean that MyISAM is better than InnoDB. Quite the contrary:
| Feature in MyISAM | Feature in InnoDB |
|---|---|
| Full-text indexes | Available in InnoDB since MySQL 5.6 |
| Portable tablespaces | Available in InnoDB since MySQL 5.6 |
| Spatial indexes | Available in InnoDB since MySQL 5.7 |
| Last update for table | Available in InnoDB since MySQL 5.7 |
In other words, as MySQL moved on, it has caught up with MyISAM, and most of the features originally only available in MyISAM have been made available in InnoDB too. Nowadays, there’s no reason to use MyISAM as it’s prone to data corruption and crashes (read the heading below), and the only real use case of MyISAM is to supplement InnoDB, but not the other way around.
Why is MyISAM Prone to Data Corruption and Crashes?
Some of you may be asking yourself “What makes MyISAM prone to crashes?” It’s such a good counter in MySQL, though, right? How can the storage engine be so bad? The core reasons for this are as follows:
Aside from counting, MyISAM shouldn’t be used for anything these days. Counting is only one part of DBA's job — a small part to begin with. Do you really want to worry about your databases constantly going down, backups corrupting your entire database if your electricity goes down, and so on just for faster COUNT queries in MySQL? InnoDB is just as capable of providing you a counter in MySQL — run from MyISAM and don’t look back.
Summary
Implementing a counter in MySQL isn’t a very hard task — those of you using MyISAM will know that the storage engine stores the internal row count inside of itself so it can return the row count more quickly than InnoDB can, but you should always be aware of the risks to your data if you use MyISAM.
Working with a COUNT(col|*) based counter in InnoDB will likely yield results slower as the storage engine doesn’t store row counts inside of itself, but your data will always be protected by the ACID mechanism.
Enjoyed this blog? Make sure to stay updated on database news by following this blog’s development on TheTable, and until next time.
FAQ
How to implement a counter in MySQL?
A counter in MySQL can be implemented by making use of the COUNT(*|col) query.
Why is COUNT(*|col) faster in MyISAM than in InnoDB?
COUNT(*) queries will always be faster on tables running the MyISAM storage engine because this storage engine stores the internal row count inside of itself and InnoDB does not.
Where can I learn more about internal functions in MySQL and other DBMSs?
Follow the development on this blog over at TheTable, and if you’re interested in video content, make sure to subscribe to the YouTube channel Database Dive.
Why should I use DbVisualizer?
Consider using the SQL client built by our team here at DbVisualizer because our SQL client is the highest-rated SQL client in the industry. Our customer reviews don’t lie — grab a free 21-day trial and try it out for yourself.
