Best way to check if record exists or not in MySQL
I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.
For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .
Query goes something like this inside procedure
set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.
mysql
edited Aug 18 '17 at 11:12
hot2use
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
add a comment |
I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.
For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .
Query goes something like this inside procedure
set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.
mysql
edited Aug 18 '17 at 11:12
hot2use
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
add a comment |
I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.
For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .
Query goes something like this inside procedure
set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.
mysql
edited Aug 18 '17 at 11:12
hot2use
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.
For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .
Query goes something like this inside procedure
set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.
mysql
mysql
edited Aug 18 '17 at 11:12
hot2use
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
edited Aug 18 '17 at 11:12
hot2use
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
edited Aug 18 '17 at 11:12
hot2use
8,42152157
edited Aug 18 '17 at 11:12
hot2use
8,42152157
edited Aug 18 '17 at 11:12
hot2use
8,42152157
8,42152157
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
asked Aug 18 '17 at 9:26
NikeshNikesh
1011210
1011210
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You could probably reduce this to 2 queries with something like:
select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result fromfound_rows()into a variable thus eliminating the undesired result set
– markp
Aug 19 '17 at 16:08
add a comment |
This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):
IF ( EXISTS( select * from table where date_field between date1 and date2 ) ;
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f183816%2fbest-way-to-check-if-record-exists-or-not-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could probably reduce this to 2 queries with something like:
select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result fromfound_rows()into a variable thus eliminating the undesired result set
– markp
Aug 19 '17 at 16:08
add a comment |
You could probably reduce this to 2 queries with something like:
select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result fromfound_rows()into a variable thus eliminating the undesired result set
– markp
Aug 19 '17 at 16:08
add a comment |
You could probably reduce this to 2 queries with something like:
select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
You could probably reduce this to 2 queries with something like:
select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
edited Aug 19 '17 at 16:05
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
answered Aug 18 '17 at 14:19
markpmarkp
2,1251215
2,1251215
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result fromfound_rows()into a variable thus eliminating the undesired result set
– markp
Aug 19 '17 at 16:08
add a comment |
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result fromfound_rows()into a variable thus eliminating the undesired result set
– markp
Aug 19 '17 at 16:08
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count.
– Nikesh
Aug 19 '17 at 4:49
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result from
found_rows() into a variable thus eliminating the undesired result set– markp
Aug 19 '17 at 16:08
@Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result from
found_rows() into a variable thus eliminating the undesired result set– markp
Aug 19 '17 at 16:08
add a comment |
This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):
IF ( EXISTS( select * from table where date_field between date1 and date2 ) ;
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
add a comment |
This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):
IF ( EXISTS( select * from table where date_field between date1 and date2 ) ;
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
add a comment |
This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):
IF ( EXISTS( select * from table where date_field between date1 and date2 ) ;
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):
IF ( EXISTS( select * from table where date_field between date1 and date2 ) ;
select * from table where date_field between date1 and date2 ;
else
select * from table order by date_field desc limit 0,20 ;
end if ;
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
edited 30 mins ago
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
answered Aug 20 '17 at 23:33
Rick JamesRick James
43.4k22259
43.4k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f183816%2fbest-way-to-check-if-record-exists-or-not-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
