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.
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.
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)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)
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 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.
A COUNT query is faster, although maybe not noticeably, but as far as getting the desired result, both should be sufficient.
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.
