Best way to check if record exists or not in 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.










share|improve this question





























    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.










    share|improve this question



























      3












      3








      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.










      share|improve this question
















      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















      share|improve this question













      share|improve this question




      share|improve this question








      edited Aug 18 '17 at 11:12









      hot2use

      8,42152157




      8,42152157










      asked Aug 18 '17 at 9:26









      NikeshNikesh

      1011210




      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


























          • 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



















          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

























            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


















            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









            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


























            • 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
















            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


























            • 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














            3












            3








            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















            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














            share|improve this answer



            share|improve this answer








            edited Aug 19 '17 at 16:05

























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











            • @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

















            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













            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






























              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




























                2












                2








                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















                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














                share|improve this answer



                share|improve this answer








                edited 30 mins ago

























                answered Aug 20 '17 at 23:33









                Rick JamesRick James

                43.4k22259




                43.4k22259






























                    draft saved

                    draft discarded




















































                    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.




                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh