3

I know that adding LIMIT 1 when searching for only one record makes a lot of difference when it comes to performance. But does it still have any effect when we search a column with a unique constraint?

Let us say I store email records with a id (int) primary key column and the email field is marked as unique. Do I still need to explicitly call LIMIT 1 in my queries to speed up the returning of a result set? Or does the MySQL engine understand that as soon as one record is found the result can be returned since the field is marked as unique and is LIMIT 1 obsolete?

MySQL table definition:

CREATE TABLE Email
(
    `id` int NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`email`)
)

Does it in this case make a difference whether I do:

SELECT * WHERE `email`="john.doe@example.com";

Or

SELECT * WHERE `email`="john.doe@example.com" LIMIT 1;
-3

if column has unique index (constraint), You do not need to use LIMIT 1

LIMIT 1 - good tools for prevent potential errors when result MUST be single and You not sure about how many rows You have

| improve this answer | |
  • 4
    The question was specifically about performance, i.e. whether adding LIMIT 1 would make a WHERE column = value query any faster if column already has a UNIQUE constraint defined on it. The OP is well aware that the query would definitely return no more than one row anyway and LIMIT 1 is unnecessary in terms of results. The question is whether LIMIT 1 would help in that scenario in terms of performance. – Andriy M Dec 16 '19 at 7:31
  • the answer was on a first row - limit 1 do not improve performance in this case :-), a lot of time since 2015 when it was answered, but it not change the fact – a_vlad Dec 17 '19 at 9:14
  • No, that answer isn't on the first or any line of your post. That's something that one could guess from your response but not something you are saying explicitly. I mean, the OP was already guessing the same thing from the beginning, but they still weren't sure and decided to ask about it. In my view, when you are asking a question like that ("I kind of think this is so but I'm not sure. Is it?"), you are asking for some evidence either confirming or denying your guess. Of course, one is still perfectly free to just answer, "Yes, you are right" or "No, you are wrong" (or imply either). :) – Andriy M Dec 17 '19 at 11:26
  • look, generalyy it is not important, but if you decide to downgrade (may be you, or somebody after you lift it) the post after 4 years since it published .. it is a principal :-) - if column contain unique index you do not need to use LIMIT 1 FOR IMPROVE performnace ... this is exactly what was asked and what was answered!!! plus may be unnecessary part about where LIMIT 1 is good to use – a_vlad Dec 17 '19 at 12:01

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.