CODEWITHSUNDEEP
mysqlsqldatabasequery-optimizationmysql-workbench
No_body
No_body

Reputation: 842

Is there a way to optimize this query

I have written a query but it's taking a lot of time. I want to know if there exists any solution to optimize it without making a temp table in MYSQL. Is there a way to optimize the subquery part since AccessLog2019 is huge so it's taking forever)

Here is my query 

SELECT distinct l.ListingID,l.City,l.ListingStatus,l.Price,l.Bedrooms,l.FullBathrooms, gc.Latitude,gc.Longitude , count(distinct s.AccessLogID) AS access_count, s.LBID , lb.CurrentListingID
from lockbox.Listings l 
JOIN lockbox.GeoCoordinates gc ON l.ListingID = gc.ID
LEFT JOIN lockbox.LockBox lb ON l.ListingID = lb.CurrentListingID
LEFT JOIN
(SELECT  * FROM  lockbox.AccessLog2019 ac where ac.AccessType not in('1DayCodeGen','BluCodeGen','SmartMACGen') AND  DATEDIFF(NOW(), ac.UTCAccessedDT ) < 1 ) s
ON lb.LBID = s.LBID
WHERE  l.AssocID = 'AS00000000CC' AND  (gc.Confidence  <> '5 - Unmatchable' OR  gc.Confidence  IS NULL OR gc.Confidence = ' ') 
group BY l.ListingID

Thanks

Upvotes: 1

Views: 93

Answers (3)

Rick James
Rick James

Reputation: 142433

  • SELECT * --> Select only the columns needed.
  • SELECT DISTINCT ... GROUP BY -- Do one or the other, not both.
  • Need composite INDEX(AssocID, ListingID) (in that order)
  • DATEDIFF(NOW(), ac.UTCAccessedDT ) < 1 --> ac.UTCAccessedDT > NOW() - INTERVAL 1 DAY (or whatever your intent was. Then add INDEX(UTCAccessedDT)
  • OR is hard to optimize; consider cleansing the data so that Confidence does not have 3 values that mean the same thing.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If you can avoid the outer group by, that is a big win. I am thinking:

SELECT l.ListingID, l.City, l.ListingStatus, l.Price, l.Bedrooms, l.FullBathrooms,
       gc.Latitude, gc.Longitude,
       (select count(*)
        from lockbox.LockBox lb join
             lockbox.AccessLog2019 ac
             on lb.LBID = ac.LBID       
        where l.ListingID = lb.CurrentListingID and
              ac.AccessType not in ('1DayCodeGen', 'BluCodeGen', 'SmartMACGen') and
              DATEDIFF(NOW(), ac.UTCAccessedDT) < 1
       ) as cnt
from lockbox.Listings l JOIN
     lockbox.GeoCoordinates gc
     ON l.ListingID = gc.ID
WHERE l.AssocID = 'AS00000000CC' AND
      (gc.Confidence  <> '5 - Unmatchable' OR 
       gc.Confidence  IS NULL OR
       gc.Confidence = ' '
      )

Note: This does not select s.LBID or lb.CurrentListingID because these don't make sense in your query. If I understand correctly, these could have different values on different rows.

Upvotes: 2

Marlin Pierce
Marlin Pierce

Reputation: 10089

You could try breaking out the subquery to the JOIN clause.

It might give a hint to the optimizer that it can use the LBID field first, and then test the AccessType later (in case the optimizer doesn't figure that out when you have the sub-select).

SELECT distinct l.ListingID,l.City,l.ListingStatus,l.Price,l.Bedrooms,l.FullBathrooms, gc.Latitude,gc.Longitude , count(distinct s.AccessLogID) AS access_count, s.LBID , lb.CurrentListingID
from lockbox.Listings l 
JOIN lockbox.GeoCoordinates gc ON l.ListingID = gc.ID
LEFT JOIN lockbox.LockBox lb ON l.ListingID = lb.CurrentListingID
LEFT JOIN AccessLog2019 s
ON lb.LBID = s.LBID
   AND s.AccessType not in('1DayCodeGen','BluCodeGen','SmartMACGen')
   AND DATEDIFF(NOW(), s.UTCAccessedDT ) < 1
WHERE  l.AssocID = 'AS00000000CC' AND  (gc.Confidence  <> '5 - Unmatchable' OR  gc.Confidence  IS NULL OR gc.Confidence = ' ') 
group BY l.ListingID

Note that this is one of those cases where conditions in the JOIN clause gives different behavior than using a WHERE clause. If you just had lb.LBID = s.LBID and then had the conditions I wrote in the WHERE of the outer query the results would be different. They would exclude the records matching lb.LBID = s.LBID. But in the JOIN clause, it is part of the conditions of the outer join.

Upvotes: 1

Related Questions