Menu
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;
646
Yes, you will notice a performance difference when dealing with the data. One record takes up less space than multiple records.
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
The LIMIT clause can restrict the result set of the query to some maximum number of rows. If this clause specifies a value smaller than the number of qualifying rows, the query returns only a subset of the rows that satisfy the selection criteria.
For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
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.
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
