2

I am using a MySQL database and perform many select like these:

SELECT MyField FROM MyTable WHERE MyKey = 'some_value';

The purpose is to retrieve a field a value if the corresponding row exists. MyKey is the only key in the table and it is indexed.

My question is: is there any benefit or drawback to adding LIMIT 0,1 to my statement?

  • Is MyKey defined as a unique index in the DDL? – Derek Downey Apr 11 '12 at 14:24
  • Yes, there is a separate index with MyKey as the only column. Does it matter? – Jérôme Verstrynge Apr 11 '12 at 14:26
3

Adding to the points raised in other answers, there can actually be a real difference!

A UNIQUE index ONLY guarantees a single result row if the column is defined as NOT NULL or your WHERE clause tests for a value that is not NULL (which it does in your example). So

SELECT MyField FROM MyTable WHERE MyKey IS NULL;

can return multiple rows, whereas adding LIMIT 0,1 would restrict that to one (random) row.

  • Nice remark, very nice remark. – Jérôme Verstrynge Apr 12 '12 at 16:55
  • Nice point on NULLABLE fields, @Jannes – Derek Downey Apr 12 '12 at 17:42
  • As an aside, Microsoft SQL's implementation is different (i.e., broken): it only allows a single NULL value in a unique field. MySQL, Oracle, and Postgresql follow ANSI SQL in this respect. – Jon of All Trades Apr 12 '12 at 19:21
3

If MyKey is NOT NULL (see Jannes answer) and defined as a UNIQUE index in the table DDL, the only benefit of adding LIMIT 0,1 is potential clarity to viewers of the query that this is only going to return a single result. Without them having to look at the DDL to know that it is unique. However, other Database designers will be looking at the DDL anyway.

If there is any overhead of adding LIMIT 0,1 it would be very, very minuscule.

Either way, adding it would be up to you. Do you need the clarity (how often do users view your queries)?

  • No users don't view the query, so I guess there is no benefit in my case to add it, except potential clarity. – Jérôme Verstrynge Apr 11 '12 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.