Sfrgttk

Things to avoid when using voltage regulators?

Make a transparent 448*448 image

Is "history" a male-biased word ("his+story")?

How could our ancestors have domesticated a solitary predator?

Is it true that real estate prices mainly go up?

How do I locate a classical quotation?

String reversal in Python

Single word request: Harming the benefactor

Replacing Windows 7 security updates with anti-virus?

How strictly should I take "Candidates must be local"?

BitNot does not flip bits in the way I expected

Is having access to past exams cheating and, if yes, could it be proven just by a good grade?

"One can do his homework in the library"

If the Captain's screens are out, does he switch seats with the co-pilot?

Should QA ask requirements to developers?

My story is written in English, but is set in my home country. What language should I use for the dialogue?

What to do when during a meeting client people start to fight (even physically) with each others?

Why don't MCU characters ever seem to have language issues?

Upside Down Word Puzzle

What are some noteworthy "mic-drop" moments in math?

Grey hair or white hair

A three room house but a three headED dog

Rejected in 4th interview round citing insufficient years of experience

What is the chance of making a successful appeal to dismissal decision from a PhD program after failing the qualifying exam in the 2nd attempt?

Best way to check if record exists or not in MySQL

Fetch first 9 records and make one more record which will be as “Others” in MySQLhow to retrieve rows of different dates using IN clauseOptimized query to fetch a record from 10 million entriesmysql - query records to fetch by time stampMysql join not workingWhy did adding a secondary index not increase select performance?Mysql multiple check and multiple update queriesBest way to structure dataMySQL Queries to get only runtime/duration, not fetched resultsReplace Subquery with JOIN - MYSQL

3

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

share|improve this question

edited Aug 18 '17 at 11:12

hot2use

8,42152157

asked Aug 18 '17 at 9:26

NikeshNikesh

1011210

add a comment | 

3

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

share|improve this question

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

share|improve this question

edited Aug 18 '17 at 11:12

hot2use

8,42152157

asked Aug 18 '17 at 9:26

NikeshNikesh

1011210

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 ;

share|improve this question

edited Aug 18 '17 at 11:12

hot2use

8,42152157

asked Aug 18 '17 at 9:26

NikeshNikesh

1011210

share|improve this question

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

asked Aug 18 '17 at 9:26

NikeshNikesh

1011210

asked Aug 18 '17 at 9:26

NikeshNikesh

1011210

2 Answers
2

active

oldest

votes

3

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 ;

share|improve this answer

edited Aug 19 '17 at 16:05

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 from found_rows() into a variable thus eliminating the undesired result set
  
  – markp
  Aug 19 '17 at 16:08
  

  
  
  
  

add a comment | 

2

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 ;

share|improve this answer

edited 10 mins ago

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
});


}
});

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%2f183816%2fbest-way-to-check-if-record-exists-or-not-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

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 ;

share|improve this answer

edited Aug 19 '17 at 16:05

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 from found_rows() into a variable thus eliminating the undesired result set
  
  – markp
  Aug 19 '17 at 16:08
  

  
  
  
  

add a comment | 

3

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 ;

share|improve this answer

edited Aug 19 '17 at 16:05

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 from found_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 ;

share|improve this answer

edited Aug 19 '17 at 16:05

answered Aug 18 '17 at 14:19

markpmarkp

2,1251215

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 ;

share|improve this answer

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

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 ;

share|improve this answer

edited 10 mins ago

answered Aug 20 '17 at 23:33

Rick JamesRick James

43.4k22259

add a comment | 

2

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 ;

share|improve this answer

edited 10 mins ago

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 ;

share|improve this answer

edited 10 mins ago

answered Aug 20 '17 at 23:33

Rick JamesRick James

43.4k22259

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 ;

share|improve this answer

edited 10 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