5

I have been doing this for quite some time:

SELECT COUNT(*) FROM Table WHERE Condition = *Condition*;

Since I am not interested in the total number of rows returned, I wonder if there is a more efficient way to check if there exist any row(s) that match the condition without letting MySQL scan through the entire table.

Question Overflow
  • 10,925
  • 18
  • 72
  • 110

2 Answers2

7
SELECT CASE
         WHEN EXISTS(SELECT *
                     FROM   YourTable
                     WHERE  Condition = '*Condition*') THEN 1
         ELSE 0
       END
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
6

Try

SELECT COUNT(*) FROM SmallTable
WHERE EXISTS(SELECT * FROM Table WHERE Condition = *Condition*)
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • +1 - similar Q here - http://stackoverflow.com/questions/424212/performance-of-sql-exists-usage-variants - Out of interest, does MySQL evaluate the inner select clause('*') used in the Exists at all? – StuartLC Oct 10 '11 at 12:05
  • [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.](http://dev.mysql.com/doc/refman/5.6/en/exists-and-not-exists-subqueries.html) – Martin Smith Oct 10 '11 at 12:06
  • It says syntax error near where exists, am I missing something here? – Question Overflow Oct 10 '11 at 12:08
  • Try adding `FROM SomeTable`, where `SomeTable` is small. (I don't know MySQL very well) – SLaks Oct 10 '11 at 12:09
  • You could do `FROM (SELECT 1) AS T` but I'd just use `CASE` myself – Martin Smith Oct 10 '11 at 12:12
  • 1
    yup there is a syntax error you need to add FROM clause there before WHERE – Gajahlemu Oct 10 '11 at 12:13
  • It didn't make my query faster and produces a column of 1s as output. – Question Overflow Oct 10 '11 at 12:14
  • Then use `COUNT(*)` on the small table. – SLaks Oct 10 '11 at 12:15