Fastest way to count number of rows in MySQL table?

Let us first see an example to create a table, add records and display them. The CREATE command is used to create a table.

mysql> CREATE table RowCountDemo
-> (
-> ID int,
-> Name varchar(100)
> );
Query OK, 0 rows affected (0.95 sec)

Records are inserted with the INSERT command.

mysql>INSERT into RowCountDemo values(1,'Larry');
Query OK, 1 row affected (0.15 sec)

mysql>INSERT into RowCountDemo values(2,'John');
Query OK, 1 row affected (0.13 sec)

mysql>INSERT into RowCountDemo values(3,'Bela');
Query OK, 1 row affected (0.15 sec)

mysql>INSERT into RowCountDemo values(4,'Jack');
Query OK, 1 row affected (0.11 sec)

mysql>INSERT into RowCountDemo values(5,'Eric');
Query OK, 1 row affected (0.19 sec)

mysql>INSERT into RowCountDemo values(6,'Rami');
Query OK, 1 row affected (0.49 sec)

mysql>INSERT into RowCountDemo values(7,'Sam');
Query OK, 1 row affected (0.14 sec)

mysql>INSERT into RowCountDemo values(8,'Maike');
Query OK, 1 row affected (0.77 sec)

mysql>INSERT into RowCountDemo values(9,'Rocio');
Query OK, 1 row affected (0.13 sec)

mysql>INSERT into RowCountDemo values(10,'Gavin');
Query OK, 1 row affected (0.19 sec)

Displaying the records.

mysql>SELECT *from RowCountDemo;

The following is the output of the above query.

+------+-------+
| ID   | Name  |
+------+-------+
| 1    | Larry |
| 2    | John  |
| 3    | Bela  |
| 4    | Jack  |
| 5    | Eric  |
| 6    | Rami  |
| 7    | Sam   |
| 8    | Maike |
| 9    | Rocio |
| 10   | Gavin |
+------+-------+
10 rows in set (0.00 sec)

To count the number of rows at a fast pace, we have the following two options −

Query 1

mysql >SELECT count(*) from RowCountDemo;

The following is the output of the above query.

+----------+
| count(*) |
+----------+
| 10       |
+----------+
1 row in set (0.00 sec)

Query 2

mysql>SELECT count(found_rows()) from RowCountDemo;

The following is the output of the above query.

+---------------------+
| count(found_rows()) |
+---------------------+
| 10                  |
+---------------------+
1 row in set (0.00 sec)

Ankith Reddy

Updated on 26-Jun-2020 12:23:37

• Related Questions & Answers
• What is the fastest way to insert a large number of rows into a MySQL table?
• Easiest way to get number of rows in a MySQL table?
• Count number of rows in each table in MySQL?
• How to get number of rows in a table without using count(*) MySQL query?
• How to count number of rows in a table with jQuery?
• Fastest Way to multiply two Numbers
• How can we use MySQL SELECT statement to count number of rows in a table?
• Fastest way to insert with multiple values in a single MySQL query?
• How to count the number of rows in a table in Selenium with python?
• MySQL - How to count all rows per table in one query?
• Fastest way to search for a date from the date records in MySQL
• Get the number of rows in a particular table with MySQL
• Fastest way of updating in MongoDB is update() or save()?
• Copy all rows of a table to another table in MySQL?
• MySQL query to count number of duplicate values in a table column