Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?

Question

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?

Answer 1

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.

Answer 2

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