Tech Tip: Use LIMIT 1 in an SQL Query When Getting a Unique Row

PRODUCT: 4D | VERSION: 11.5 | PLATFORM: Mac & Win

Published On: January 25, 2010

Sometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might just be just checking the existence of any number of records that satisfy your WHERE clause.

In such cases, adding LIMIT 1 to your query can increase performance. This way the database engine will stop scanning for records after it finds just 1, instead of going through the whole table or index.

The following two examples show how to not test for the existence of users, and then how to test more efficiently.

Example 1: How not to do it

` do I have any users from Alabama?
BEGIN SQL
    SELECT name
    FROM user
    WHERE state = 'Alabama'
    INTO :myVarArr;
END SQL
If (Size of array(myVarArr) > 0)
    `// ...
end if

Example 2: How to do it more efficient

` do I have any users from Alabama?
BEGIN SQL
    SELECT name
    FROM user
    WHERE state = 'Alabama'
    LIMIT 1
    INTO :myVar;
END SQL
If (myVar # "")
    `// ...
End if

This is equivalent to using the SET QUERY LIMIT command in 4D.