Efficient Query to Check if MySQL Table is Empty: COUNT vs LIMIT

If you use COUNT(*) around the LEAST() then MySQL scans at least one index, therefore avoid LEAST(COUNT(*)) and use LIMIT.

Let us first create a table. The query to create a table is as follows −

mysql> create table ReturnDemo
-> (
-> Id int,
-> Name varchar(10)
-> );
Query OK, 0 rows affected (0.79 sec)

Example

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into ReturnDemo values(100,'Larry');
Query OK, 1 row affected (0.18 sec)
mysql> insert into ReturnDemo values(101,'Bob');
Query OK, 1 row affected (0.28 sec)
mysql> insert into ReturnDemo values(102,'Sam');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from ReturnDemo;

Output

+------+-------+
|   Id | Name  |
+------+-------+
|  100 | Larry |
|  101 | Bob   |
|  102 | Sam   |
+------+-------+
3 rows in set (0.00 sec)

The following is the query to check whether a table is empty or not −

mysql> SELECT 1 AS Output FROM ReturnDemo LIMIT 1;

The following is the output displaying 1 i.e. the table isn’t empty −

+--------+
| Output |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)