Best way to test if a row exists in a MySQL table

Question

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

User · Answer

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 ...

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

A short example of  ChrisThompson s answer  Example   mysql gt  SELECT   FROM table 1   ---- --------    id   col1      ---- --------     1   foo         2   bar         3   foobar    ---- --------  3 rows in set  0 00 sec   mysql gt  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 gt  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 gt  SELECT EXISTS SELECT 1 FROM table 1 WHERE id   1  AS mycheck   ---------    mycheck    ---------          1    ---------  1 row in set  0 00 sec

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

COUNT    are optimized in MySQL  so the former query is likely to be faster  generally speaking

User · Answer

For non-InnoDB tables you could also use the information schema tables   http   dev mysql com doc refman 5 1 en tables-table html

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

Examples related to sql

Examples related to mysql

Examples related to performance

Examples related to exists