🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

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

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.
mysql sql
by

3 Answers

aashaykumar
499

You could also try EXISTS:


SELECT EXISTS(SELECT  FROM table1 WHERE ...)

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.
sandhya6gczb
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 ...
pankajshivnani123
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)

Login / Signup to Answer the Question.

LoginSignup