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
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:
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.
Source: Stackoverflow.com