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)
Published on 20-Nov-2018 11:08:15
