Check if MySQL Table is empty: COUNT(*) is zero vs. LIMIT(0,1) has a result?

Question

This is a simple question about efficiency specifically related to the MySQL implementation. I want to just check if a table is empty (and if it is empty, populate it with the default data). Would it be best to use a statement like SELECT COUNT(*) FROM `table` and then compare to 0, or would it be better to do a statement like SELECT `id` FROM `table` LIMIT 0,1 then check if any results were returned (the result set has next)?

Although I need this for a project I am working on, I am also interested in how MySQL works with those two statements and whether the reason people seem to suggest using COUNT(*) is because the result is cached or whether it actually goes through every row and adds to a count as it would intuitively seem to me.

Answer 1

You should definitely go with the second query rather than the first.

When using COUNT(*), MySQL is scanning at least an index and counting the records. Even if you would wrap the call in a LEAST() (SELECT LEAST(COUNT(*), 1) FROM table;) or an IF(), MySQL will fully evaluate COUNT() before evaluating further. I don't believe MySQL caches the COUNT(*) result when InnoDB is being used.

Your second query results in only one row being read, furthermore an index is used (assuming id is part of one). Look at the documentation of your driver to find out how to check whether any rows have been returned. By the way, the id field may be omitted from the query (MySQL will use an arbitrary index):

SELECT 1 FROM table LIMIT 1;

However, I think the simplest and most performant solution is the following (as indicated in Gordon's answer):

SELECT EXISTS (SELECT 1 FROM table);

EXISTS returns 1 if the subquery returns any rows, otherwise 0. Because of this semantic MySQL can optimize the execution properly. Any fields listed in the subquery are ignored, thus 1 or * is commonly written.

See the MySQL Manual for more info on the EXISTS keyword and its use.

Answer 2

It is better to do the second method or just exists. Specifically, something like:

if exists (select id from table)

should be the fastest way to do what you want. You don't need the limit; the SQL engine takes care of that for you.

By the way, never put identifiers (table and column names) in single quotes.