Best way to test if a row exists in a MySQL table
Asked Answered
H

12

421

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.

Headcloth answered 4/11, 2009 at 20:55 Comment(0)
S
563

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.

Smokestack answered 4/11, 2009 at 20:59 Comment(9)
Test with ...EXISTS( SELECT 1/0 FROM someothertable). For SQL Server & Oracle - it makes no difference to use *, 1 or NULL because EXISTS only tests for a boolean based on 1+ of the WHERE criteria matching.Lynx
Guys, it says right in the documentation linked to in this answer, 2nd paragraph, "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."Telegu
@ChrisThompson : what happens when the statement is executed? I mean what does the result set contain?Grisette
@Ashwin, it contains whether a 0 (not exists) or 1 (exists).Codger
Does the statement return bool type or unsigned char type? I am developing using the C API and I need to know this. Thanks.Vlaminck
I think your query is superfluous, I tested, and this query SELECT 1 FROM table1 WHERE col = $var LIMIT 1 is more faster than your query. So what is the advantage of your query?Programmer
@Programmer makes an excellent point, that is easy to overlook. Missing as an option above is SELECT 1 FROM test WHERE ... LIMIT 1, without SELECT EXISTS around it. Might be a hair faster that way.Laminous
To avoid a very long key, use this SELECT EXISTS(SELECT 1 FROM table1 WHERE ...) as 'is_exist'Goldy
@Programmer The EXISTS() version has the advantage to return 0 instead of NULL when used as a scalar subquery such that you can add multiple of these expressions to create an "or" expression from it.Southeasterly
A
247

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)
Afterdamp answered 21/5, 2012 at 15:23 Comment(8)
Thanks for the additional answer. Did you find the difference in time between the two fastest options for a TEXT field to be pretty consistent? The difference doesn't seem large, and using SELECT EXISTS(SELECT 1 ... LIMIT 1) seems to be pretty good in both cases.Headcloth
You are right, the difference is not so important regarding the other results concerning the text field. Nevertheless, maybe the query would be better using SELECT 1 FROM test WHERE texte LIKE '%something%' LIMIT 1Afterdamp
I tried on mysql and in the case you use select 1 ... limit 1, it is useless to surround with select existsTavis
@LittleNooby there is difference. SELECT EXISTS ... gives true and false value (1 or 0), while SELECT 1 ... gives either 1 or empty. There are subtle difference between false value and empty set, depending on your situation.Prediction
@LittleNooby makes an excellent point, that is easy to overlook. Missing in the timing tests above is SELECT 1 FROM test WHERE ..., without SELECT EXISTS around it. Presumably is a hair faster that way.Laminous
I believe SELECT *... becomes slower as you add more columns. I would be curious how this all compares to SELECT id...Navarrette
This is almost certainly because the PK select can be satisfied by the index for SELECT 1, but not for SELECT * which requires a row-lookup. The TEXT select needs a row lookup either way, because of the condition. SELECT 1 FROM ... WHERE ... LIMIT 1 will give you the best of both worlds.. add the EXISTS wrapping if you require a boolean return for no matching rows.Perilune
Which version of MySQL did you use? At least in 5.5+ there is no difference between EXISTS (SELECT ...) and EXISTS (SELECT ... LIMIT 1). MySQL is smart enough to insert this LIMIT 1 by itself, because this is how EXISTS works: it stops when at least one result is found.Birefringence
D
36

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)
Depurative answered 19/12, 2013 at 11:18 Comment(1)
While the accepted answer was helpful, I appreciated the alias syntax. Thanks!Wearisome
P
28

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) 
Premonitory answered 15/2, 2017 at 6:32 Comment(1)
These numbers are meaningless unless you have complete control of the universe. For one thing try doing them in reverse order. Unless of course your point is there is no difference. In that case you probably right.Protocol
P
22

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.

Perilune answered 8/3, 2017 at 16:7 Comment(0)
G
5

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.

Goulden answered 2/11, 2012 at 15:21 Comment(0)
G
3

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 ...
Gregggreggory answered 22/3, 2016 at 6:50 Comment(1)
Why not just use IFNULL(id, 0) here instead of the COUNT(*)?Assembled
F
2

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

Fearfully answered 4/11, 2009 at 20:58 Comment(2)
This is however DB specific. The COUNT(*) is known to be slow in PostgreSQL. Better would be to select the PK column and see if it returns any rows.Astrix
COUNT(*) is slow in InnoDB thoughSokul
A
-1

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

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

Anticipant answered 4/11, 2009 at 21:12 Comment(0)
F
-1

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.

Fainthearted answered 4/11, 2009 at 21:15 Comment(2)
-1 This is wrong. COUNT(*) doesn't look at the column values - it just counts the number of rows. See my answer here: #2877409Gondar
COUNT() is much slower than EXISTS as EXISTS can return when it first finds a rowSokul
P
-1

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)')

Partridge answered 29/10, 2014 at 9:8 Comment(0)
A
-3

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

Aboral answered 4/11, 2009 at 21:1 Comment(1)
Are you referring to the optimization that the MyISAM has for selecting the count for a whole table? I didn't think that helped if there was a WHERE condition.Headcloth

© 2022 - 2024 — McMap. All rights reserved.