Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  JVerstry Apr 11 '12 at 14:26

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Nice remark, very nice remark. –  JVerstry 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

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

share|improve this answer
    
No users don't view the query, so I guess there is no benefit in my case to add it, except potential clarity. –  JVerstry Apr 11 '12 at 14:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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