Search code examples
mysqlquery-optimizationexistssql-limit

Any point in using LIMIT in EXISTS query?

Is there any performance benefit in adding a LIMIT to an EXISTS query, or would MySQL apply the limit on its own?

Example:

IF EXISTS (
    SELECT 1
      FROM my_table
     LIMIT 1    -- can this improve performance?
)
THEN ... END IF;
Solution

  • The purpose of EXISTS() is to perform the query only until it can decide if there are any rows in that table matching the WHERE clause. That is, it logically does the same thing as LIMIT 1. EXISTS is probably called semi-join in some circles.

    Bottom line: Don't use LIMIT 1 inside EXISTS().

    Addenda: As Paul points out, a LIMIT with an OFFSET (or LIMIT m,n) does have meaning.