Asked  7 Months ago    Answers:  5   Viewed   55 times

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.

 Answers

99

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.

Tuesday, June 1, 2021
 
Jimenemex
answered 7 Months ago
96

Option 3 is the fastest way to check if a row exists if you are using MySQL:

$query = mysql_query("SELECT EXISTS(SELECT 1 FROM users WHERE id = 1)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else
Saturday, May 29, 2021
 
adjco
answered 7 Months ago
67

Keep in mind that default(string) is null, not string.Empty. You may want a special case in your code:

if (typeof(T) == typeof(String)) return (T)(object)String.Empty;
Sunday, August 1, 2021
 
Asnexplore
answered 5 Months ago
84

No. In MySQL, "databases" are essentially just catalogues which have no effect on the way data are stored or queried.

Querying two tables in different databases is the same as querying two tables in the same db, from a query execution standpoint.

Friday, August 13, 2021
 
gMale
answered 4 Months ago
91

Both perform the same, because the SELECT clause in the EXISTS is never evaluated. You can test using:

... EXISTS(SELECT 1/0 FROM TABLE) 

That should trigger a divide by zero error, but won't.

I personally prefer using NULL because it's obvious that nothing is referenced in the table, so it's more visible to others. Selecting a value, like the INT number 1 in the second example, can lead to assumptions about what is happening if not familiar with the EXISTS clause.

Thursday, November 11, 2021
 
icza
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share