[sql] Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this:

SELECT COUNT(*) AS total FROM table1 WHERE ...

and check to see if the total is non-zero or is it better to do a query like this:

SELECT * FROM table1 WHERE ... LIMIT 1

and check to see if any rows were returned?

In both queries, the WHERE clause uses an index.

This question is related to sql mysql performance exists

The answer is


I have made some researches on this subject recently. The way to implement it has to be different if the field is a TEXT field, a non unique field.

I have made some tests with a TEXT field. Considering the fact that we have a table with 1M entries. 37 entries are equal to 'something':

  • SELECT * FROM test WHERE text LIKE '%something%' LIMIT 1 with mysql_num_rows() : 0.039061069488525s. (FASTER)
  • SELECT count(*) as count FROM test WHERE text LIKE '%something% : 16.028197050095s.
  • SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%') : 0.87045907974243s.
  • SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%' LIMIT 1) : 0.044898986816406s.

But now, with a BIGINT PK field, only one entry is equal to '321321' :

  • SELECT * FROM test2 WHERE id ='321321' LIMIT 1 with mysql_num_rows() : 0.0089840888977051s.
  • SELECT count(*) as count FROM test2 WHERE id ='321321' : 0.00033879280090332s.
  • SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321') : 0.00023889541625977s.
  • SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1) : 0.00020313262939453s. (FASTER)

I feel it is worth pointing out, although it was touched on in the comments, that in this situation:

SELECT 1 FROM my_table WHERE *indexed_condition* LIMIT 1

Is superior to:

SELECT * FROM my_table WHERE *indexed_condition* LIMIT 1

This is because the first query can be satisfied by the index, whereas the second requires a row look up (unless possibly all the table's columns are in the index used).

Adding the LIMIT clause allows the engine to stop after finding any row.

The first query should be comparable to:

SELECT EXISTS(SELECT * FROM my_table WHERE *indexed_condition*)

Which sends the same signals to the engine (1/* makes no difference here), but I'd still write the 1 to reinforce the habit when using EXISTS:

SELECT EXISTS(SELECT 1 FROM my_table WHERE *indexed_condition*)

It may make sense to add the EXISTS wrapping if you require an explicit return when no rows match.


Suggest you not to use Count because count always makes extra loads for db use SELECT 1 and it returns 1 if your record right there otherwise it returns null and you can handle it.


For non-InnoDB tables you could also use the information schema tables:

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html


COUNT(*) are optimized in MySQL, so the former query is likely to be faster, generally speaking.


A short example of @ChrisThompson's answer

Example:

mysql> SELECT * FROM table_1;
+----+--------+
| id | col1   |
+----+--------+
|  1 | foo    |
|  2 | bar    |
|  3 | foobar |
+----+--------+
3 rows in set (0.00 sec)

mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1);
+--------------------------------------------+
| EXISTS(SELECT 1 FROM table_1 WHERE id = 1) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 9);
+--------------------------------------------+
| EXISTS(SELECT 1 FROM table_1 WHERE id = 9) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

Using an alias:

mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
+---------+
| mycheck |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

At times it is quite handy to get the auto increment primary key (id) of the row if it exists and 0 if it doesn't.

Here's how this can be done in a single query:

SELECT IFNULL(`id`, COUNT(*)) FROM WHERE ...

In my research, I can find the result getting on following speed.

select * from table where condition=value
(1 total, Query took 0.0052 sec)

select exists(select * from table where condition=value)
(1 total, Query took 0.0008 sec)

select count(*) from table where condition=value limit 1) 
(1 total, Query took 0.0007 sec)

select exists(select * from table where condition=value limit 1)
(1 total, Query took 0.0006 sec) 

I'd go with COUNT(1). It is faster than COUNT(*) because COUNT(*) tests to see if at least one column in that row is != NULL. You don't need that, especially because you already have a condition in place (the WHERE clause). COUNT(1) instead tests the validity of 1, which is always valid and takes a lot less time to test.


Or you can insert raw sql part to conditions so I have 'conditions'=>array('Member.id NOT IN (SELECT Membership.member_id FROM memberships AS Membership)')


A COUNT query is faster, although maybe not noticeably, but as far as getting the desired result, both should be sufficient.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to performance

Why is 2 * (i * i) faster than 2 * i * i in Java? What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism? How to check if a key exists in Json Object and get its value Why does C++ code for testing the Collatz conjecture run faster than hand-written assembly? Most efficient way to map function over numpy array The most efficient way to remove first N elements in a list? Fastest way to get the first n elements of a List into an Array Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? pandas loc vs. iloc vs. at vs. iat? Android Recyclerview vs ListView with Viewholder

Examples related to exists

Select rows which are not present in other table How to fix "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error? Check if record exists from controller in Rails sql: check if entry in table A exists in table B How to exclude records with certain values in sql select SQL - IF EXISTS UPDATE ELSE INSERT INTO Linq select objects in list where exists IN (A,B,C) PL/pgSQL checking if a row exists How to Check if value exists in a MySQL database MongoDB: How to query for records where field is null or not set?