KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
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
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

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.