Any point in using LIMIT in EXISTS query?

Question

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;

yeah ...This improve performance because you are limiting records. — Abhishek Ginani, Jan 13 '16 at 05:43
@TimBiegeleisen, there are many rows in `my_table`, if that's what you mean. — shmosel, Jan 13 '16 at 05:53
@AbhishekGinani I don't think so. SQL is smart enough to do only what's needed. It stops once it finds ANY record. That's the purpose of it. — Robo Robok, Mar 22 '18 at 14:29

Rick James · Accepted Answer · 2019-02-18T01:55:48.083

29

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.

edited Feb 18 '19 at 01:55

answered Jan 13 '16 at 21:04

Rick James

135,179
13
127
222

2

LIMIT seems to be ignored in an EXISTS subquery. But you would need it, if you want to define an OFFSET, when you want to check if at least N rows exist. – Paul Spiegel Feb 18 '19 at 01:28

score 3 · Answer 2 · answered Jan 13 '16 at 05:49

3

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.

answered Jan 13 '16 at 05:49

shmosel

49,289
6
73
138

I suggest that `LIMIT 0` is an aberrant edge case. – Rick James Jan 13 '16 at 21:05
1

It is a proof of concept case – Luca C. Sep 25 '18 at 07:53

score 0 · Answer 3 · answered Jan 13 '16 at 06:48

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).

Any point in using LIMIT in EXISTS query?

3 Answers3

Linked