Using LIMIT when searching by a unique field

Question

Given a table structure like this:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `username` varchar(16) NOT NULL,
  `password` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
);

Is there any use in using the LIMIT keyword when searching by username, or is the DB smart enough to know that there can only possibly be one result, and therefore stop searching once it's found one?

SELECT * FROM `user` WHERE `username` = 'nick';

-- vs --

SELECT * FROM `user` WHERE `username` = 'nick' LIMIT 1;

---

Update: Thanks for the answers, they've been enlightening. It seems like, even though it's unnecessary, putting LIMIT 1 on the query doesn't hurt, and probably increases readability (you don't have to go looking into the DB schema to know that only one is going to be returned). Special shoutout for JR's answer - I didn't even know you could do that with indices.

Also, there's a similar question I've found here, which might also help.

Answer 1

The sql query optimizer should be smart enough to figure this out.

Answer 2

I've always been told and read that you should include the LIMIT everytime you only want 1 result. This just tells the DB that it should stop so matter what. In your case, you're probably right it doesn't make a difference, but I think it's better just to always do than always deciding and leaving it out one time when you need it.

Answer 3

There is no need to use LIMIT.

However... If you have a HUGE HUGE HUGE cardinality on the table (and you're worried about speed), you might consider not using the special UNIQUE constraint in the DB and manage it from your application. Then, you can specify an INDEX on only the first few characters of your username field. This will drastically reduce the size of your index, being sure the whole thing fits into RAM, and possibly speed up your queries.

So, you might try:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `username` varchar(16) NOT NULL,
  `password` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`(4))
);

as an alternative if your first attempt turns out to be too slow. This means, of course, you have to check for duplicate user names before inserting, but you'll probably have to do that anyway to tell the user they have to pick another user name.

Depending on what DB server and engine you're using, it may also be faster to specify fixed width fields for all your strings also. Instead of varchar, use char.