Which query is efficient 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)

Samual Sam

Published on 19-Mar-2019 08:01:05

• Related Questions & Answers
• MySQL query to check if database is empty or not?
• Execute INSERT if table is empty in MySQL?
• Check if a table is empty or not in MySQL using EXISTS
• Python - Check if dictionary is empty
• How to check if field is null or empty in MySQL?
• How to check if android editText is empty?
• Check if value is empty in JavaScript
• How to check if String is empty in Java?
• How to check if a C# list is empty?
• How to check if PSCustomObject is empty in PowerShell?
• Check if a HashMap is empty in Java
• Python Pandas - Check if an interval is empty
• Which technique is more efficient for replacing duplicate records in MySQL?
• MySQL query to delete table rows if string in cell is matched
• How to check if a list is empty in Python?