Sfrgttk

Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?

How do you keep chess fun when your opponent constantly beats you?

What other Star Trek series did the main TNG cast show up in?

Why are PDP-7-style microprogrammed instructions out of vogue?

"is" operation returns false with ndarray.data attribute, even though two array objects have same id

Match Roman Numerals

Why doesn't a hydraulic lever violate conservation of energy?

What was the last x86 CPU that did not have the x87 floating-point unit built in?

Huge performance difference of the command find with and without using %M option to show permissions

"... to apply for a visa" or "... and applied for a visa"?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

What information about me do stores get via my credit card?

Why can't devices on different VLANs, but on the same subnet, communicate?

Did the new image of black hole confirm the general theory of relativity?

How do I design a circuit to convert a 100 mV and 50 Hz sine wave to a square wave?

Windows 10: How to Lock (not sleep) laptop on lid close?

Do working physicists consider Newtonian mechanics to be "falsified"?

Keeping a retro style to sci-fi spaceships?

What to do when moving next to a bird sanctuary with a loosely-domesticated cat?

What is the padding with red substance inside of steak packaging?

Python - Fishing Simulator

Single author papers against my advisor's will?

How to determine omitted units in a publication

What's the point in a preamp?

Does LIMIT 1 still have effect on a unique column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

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;

mysql performance limits unique-constraint

share|improve this question

edited Nov 16 '15 at 12:57

Wilt

asked Nov 16 '15 at 9:13

WiltWilt

1114

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

1

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;

mysql performance limits unique-constraint

share|improve this question

edited Nov 16 '15 at 12:57

Wilt

asked Nov 16 '15 at 9:13

WiltWilt

1114

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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;

mysql performance limits unique-constraint

share|improve this question

edited Nov 16 '15 at 12:57

Wilt

asked Nov 16 '15 at 9:13

WiltWilt

1114

CREATE TABLE Email

(

    `id` int NOT NULL,

    `email` varchar(255) NOT NULL,

    PRIMARY KEY (`id`),

    UNIQUE (`email`)

)

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

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

share|improve this question

edited Nov 16 '15 at 12:57

Wilt

asked Nov 16 '15 at 9:13

WiltWilt

1114

share|improve this question

edited Nov 16 '15 at 12:57

Wilt

asked Nov 16 '15 at 9:13

WiltWilt

1114

asked Nov 16 '15 at 9:13

WiltWilt

1114

asked Nov 16 '15 at 9:13

WiltWilt

1114

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

edited Nov 16 '15 at 10:57

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f121203%2fdoes-limit-1-still-have-effect-on-a-unique-column%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Nov 16 '15 at 10:57

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

add a comment | 

0

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

share|improve this answer

edited Nov 16 '15 at 10:57

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

add a comment | 

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

share|improve this answer

edited Nov 16 '15 at 10:57

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

share|improve this answer

edited Nov 16 '15 at 10:57

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716

answered Nov 16 '15 at 10:35

a_vlada_vlad

3,0421716