1

I am having unexpected results from MariaDB IF EXISTS... in a stored procedure. I created a minimal example to demonstrate the issue:

    PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 CHAR(2))
BEGIN

  IF EXISTS(SELECT COUNT(*) FROM test WHERE intval = pParam1 AND text = pParam2) THEN
    SELECT 'Found' AS result;
  ELSE
    SELECT 'Not Found' AS result;
  END IF;

END

No matter what the contents of the file "test" or the parameters I give to the call, this procedure always returns "Found".

Am I doing something wrong?

Thanks.

Improve this question
0

1 Answer 1

Reset to default
1

SELECT COUNT(*) always returns at least one record (maybe with zero). So IF EXISTS is always true.

You may:

  • Replace SELECT COUNT(*) ... with SELECT 1 ...

  • Replace IF EXISTS(SELECT ... with IF 0 < (SELECT ...

I think that 1st variant is more fast. It only checks does at least one record exists, and so it must stop searching after first record found, whereas 2nd query must obtain all matched records, count them, and then compare the result with zero.

Improve this answer
3
  • Where it makes a difference, IF EXISTS (SELECT * FROM ... ) will be faster than IF (SELECT COUNT(*) FROM ... )>0` or similar. If the optimiser doesn't spot the equivalence then it may scan an index to make the count to compare to zero, where the explicit EXISTS clause can abort early: as soon as one matching row has been found you know the result of the IF is going to be true so further reading is unneeded. This is true for the simplest case IF EXISTS(SELECT * FROM SimpleTable) and for cases involving filtering clauses, joining clauses, and other complications.
    – David Spillett
    Nov 20, 2019 at 14:56
  • 1
    Also generally speaking IF EXISTS (SELECT 1 ... is no faster than IF EXISTS (SELECT * ... (this is certainly true for MS SQL Server) though it may depend on which database engine you are using. There should be no circumstance where SELECT 1 is slower though, so you may chose to prefer that just-in-case. See the answers on dba.stackexchange.com/questions/159413/… for a more detailed run-down.
    – David Spillett
    Nov 20, 2019 at 15:02
  • IF EXISTS ( SELECT 1 ... ) does a semi-join (stop when first row found), so it is inherently faster than finding all the rows to count them.
    – Rick James
    Nov 20, 2019 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.