Best way to test if a row exists in a MySQL table
SqlMysqlPerformanceExistsSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
You could also try EXISTS:
SELECT EXISTS(SELECT * FROM table1 WHERE ...)
and per the documentation, you can SELECT anything.
> Traditionally, an EXISTS subquery starts with SELECT *, but it could > begin with SELECT 5 or SELECT column1 or anything at all. MySQL > ignores the SELECT list in such a subquery, so it makes no difference.
Solution 2 - Sql
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 1withmysql_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 1withmysql_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)
Solution 3 - Sql
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)
Solution 4 - Sql
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)
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
A COUNT query is faster, although maybe not noticeably, but as far as getting the desired result, both should be sufficient.
Solution 8 - Sql
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 ...
Solution 9 - Sql
For non-InnoDB tables you could also use the information schema tables:
Solution 10 - Sql
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.
Solution 11 - Sql
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)')
Solution 12 - Sql
COUNT(*) are optimized in MySQL, so the former query is likely to be faster, generally speaking.