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;

回答1:

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.

回答2:

Fiddling with my query a bit, I noticed that EXISTS still returns 1 if LIMIT is set to 0. I assume this indicates that it's being ignored.

回答3:

This depends on how many records in your table(my_table).If records are not too much then you will not see any performance improvement but if your table has too much records then you will see the performance improvement but this will also depends on many factor as do you have index in the column those are being used in select(if you will do this then you will get the benefit of covering index also).