I have a query which works fine, but takes too long to execute or sometimes even ends up giving error message saying lost connection to the mysql database as the query took too long. Here is the query:

SELECT
  EventNum,EventName,StartDate,EndDate,EventHours,
  Theme,Promoter,GenInfoPhone,GenInfoPhone2,LocationStreet1,
  LocationStreet2,LocationCity,LocationState,LocationCounty,
  Attendance,WebSite,MySpaceAddr,Twitter,Facebook,
  blog,rssfeedlink,utube,TicketEmail,TicketInfo,
  AdmissCharge,ParkingCharge,pets,NewsLink1,
  NewsTitle1,NewsLink2,NewsTitle2,NewsDate1,NewsDate2,
  Author1,AuthorEmail1,Author2,AuthorEmail2,
  adLink,Descriptionsmall,eventType,eventType2,eventType3, FairPhotos.file_name
  FROM FairListings
  LEFT OUTER JOIN FairPhotos ON FairListings.EventNum = FairPhotos.eventID
  WHERE EndDate >= '2019-05-03'
    and EndDate <= DATE_ADD( NOW( ) , INTERVAL +10 YEAR )
    and delRecord = 0
    AND ( eventType  LIKE 'cfair'
       OR eventType2 LIKE 'cfair'
       OR eventType3 LIKE 'cfair')
  ORDER BY StartDate
  LIMIT 30

Notice the last two clauses ORDER BY StartDate LIMIT 30. If I remove ORDER BY clause it runs perfectly and takes hardly 0.2 secs to execute. Even if I remove LIMIT 30, it returns 729 rows and without ORDER BY it runs smoothly and fast. Problem is I need the ORDER BY startdate clause. I tried putting LIMIT 30 before ORDER BY like LIMIT 30 ORDER BY StartDate, but it is wrong syntax. I am a noob when it comes to mysql. Can anybody give a solution. I want a single query solution not multiple queries as answered by eddedeed in this thread Why is my SQL Server ORDER BY slow despite the ordered column being indexed?

All help is appreciated.

EDIT

After doing some research I think that FairPhotos table is causing issue while ordering. It has a column by the name upload_file which is of type mediumblob. It contains the actual uploaded image file, which is stored in the form of blob. If I change my query to this

SELECT  EventNum,EventName,StartDate,EndDate,EventHours, Theme,
        Promoter,GenInfoPhone,GenInfoPhone2,LocationStreet1, LocationStreet2,
        LocationCity,LocationState,LocationCounty, Attendance,
        WebSite,MySpaceAddr,Twitter,Facebook, blog,rssfeedlink,
        utube,TicketEmail,TicketInfo, AdmissCharge,ParkingCharge,
        pets,NewsLink1, NewsTitle1,NewsLink2,NewsTitle2,NewsDate1,
        NewsDate2, Author1,AuthorEmail1,Author2,AuthorEmail2,
        adLink,Descriptionsmall,eventType,eventType2,eventType3
    FROM  FairListings
    WHERE  EndDate >= '2019-05-03'
      and  EndDate <= DATE_ADD( NOW( ) , INTERVAL +10 YEAR )
      and  delRecord = 0
      AND  ( eventType LIKE  'cfair'
         OR  eventType2 LIKE 'cfair'
         OR  eventType3 LIKE 'cfair'
           )
    ORDER BY  StartDate

Notice I have removed these chunks , FairPhotos.file_name and LEFT OUTER JOIN FairPhotos ON FairListings.EventNum = FairPhotos.eventID from the original query. It still contains ORDER BY StartDate but no LIMIT 30.

This new query works like a charm even with ORDER BY and without LIMIT 30. It returns 779 rows and took around 0.02 secs to execute.

Here is the screenshot of the FairPhotos table

enter image description here

Do you think that update_file field is too large to be sorted even when I am not selecting it?

0 Answers