MySQL
SQL

Counter in MySQL: Counting Rows with Ease

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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):

Total Row Count in phpMyAdmin
Total Row Count in phpMyAdmin

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:

The number of rows in the `demo_data` Table with DbVisualizer
The number of rows in the `demo_data` Table with DbVisualizer

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:

Running a Basic `COUNT(*)` Query in DbVisualizer
Running a Basic `COUNT(*)` Query in DbVisualizer

Our table has 985,920 records. Cool. How does this counter in MySQL work, though? Everything’s more simple than you can imagine (sometimes):

  1. If you run the MyISAM storage engine on any of your tables (MyISAM is obsolete and shouldn’t be used — consider a switch to InnoDB by executing the query below instead), MySQL will read the row count within a table by reading the metadata from the storage engine.
  2. If you run the InnoDB storage engine on any of your tables, MySQL will attempt to count the rows and return the result — this is why such queries on large InnoDB tables can take a lot of time to execute and return the necessary results.

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):

Copy
        
1 SELECT COUNT(column) FROM table;
COUNT(col) in Action in MySQL
COUNT(col) in Action in MySQL

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:

Copy
        
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:

Copy
        
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 MyISAMFeature in InnoDB
Full-text indexesAvailable in InnoDB since MySQL 5.6
Portable tablespacesAvailable in InnoDB since MySQL 5.6
Spatial indexesAvailable in InnoDB since MySQL 5.7
Last update for tableAvailable 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:

  • MyISAM doesn’t maintain a file that it can “look at” when restoring your data from a crash. InnoDB does. That means that InnoDB can look at the log files when restoring your data from a crash, and since MyISAM simply doesn’t maintain them, it cannot do the same thing.
  • InnoDB offers ACID compliance — MyISAM does not. InnoDB is one of the few storage engines in MySQL, MariaDB, and Percona Server that can offer full-fledged ACID compliance. That ACID compliance can be turned off and exchanged for speed in return for a higher risk of losing up to one second’s worth of transactions (fiddle with the innodb_flush_log_at_trx_commit parameter and set it to 0, 2, MariaDB also supports the option 3), but MyISAM doesn’t have any of such things.
  • MyISAM does offer a key cache that may make inserts into MyISAM tables faster (it’s function is essentially the same as the innodb_buffer_pool in InnoDB), but the key cache can only be optimized so far and the data in your databases will always make you worried — if electricity goes out mid-insert, your data is gone.

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.

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 liegrab a free 21-day trial and try it out for yourself.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29
title

Everything You Need to Know About SQL Constraints: The What, Why, and How

author Lukas Vileikis tags BEGINNER MySQL SQL 8 min 2024-10-28
title

Changing the root Password in MySQL: A Guide

author Lukas Vileikis tags MySQL SECURITY 6 min 2024-10-22
title

Database Replication in MySQL: Ensuring Data Consistency and Availability

author Ochuko Onojakpor tags Database replication MySQL 12 min 2024-10-21
title

A Complete Guide to the MySQL Boolean Type

author Antonello Zanini tags MySQL 8 min 2024-10-17
title

How To Format SQL Query Strings

author Antonello Zanini tags SQL 7 min 2024-10-16
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗