Results 1 to 3 of 3
Feb 22, 2013, 09:12 #1
- Join Date
- Apr 2012
- 0 Post(s)
- 0 Thread(s)
Query taking >150 seconds to execute ... Need some optimization help
I suspect the problem is that I'm using the wrong type of joins. Essentially the only data I actually want returned, is from the reco table with only 2 pieces of data from the user table and 1 piece from the user status table.
The rest of the joins and conditions are for the purpose of either filters or inclusions. eg. Include recommendations from user 144, or where a users status id is 4, but then there's 'requirements' like the reco must be active, the user who posted the reco must be active.
Any suggestions would be GREATLY appreciated, I need to bring this execution time down to under 10 seconds. :|
SELECT r.iRecommendationId, r.iUserId, r.iCategoryId, r.fLat as rfLat, r.fLon as rfLon, r.vNeed_To_Know, round((6371 * acos(cos(radians(43.64830)) * cos(radians(r.fLat)) * cos(radians(r.fLon) - radians(- 79.402400)) + sin(radians(43.64830)) * sin(radians(r.fLat)))), 2) AS recommendationDistance, substring(r.vRecommendation, 1, 42) AS vRecommendation, r.iRerecommendId, r.vRPicture, r.vMetaDataObject, r.vUrl, r.vLocation, r.root, u.vPicture, u.vUserName, s.imStatusId FROM msy_recommendations AS r INNER JOIN msy_users AS u ON r.iUserId = u.iUserId AND u.eStatus = 'Active' INNER JOIN msy_featured_user as f ON f.iUserId = r.iUserId INNER JOIN msy_users_status AS s ON r.iUserId = s.iUserId LEFT OUTER JOIN msy_subscriptions as sub ON sub.imuid = r.iUserId LEFT JOIN msy_bookmarks as b ON (b.iRecommendationId = r.iRecommendationId AND b.iUserId = 853) WHERE (r.eStatus = 'Active' AND r.eRerecommend = 'Enable' AND r.iCategoryId IN(3,5,6)) AND (r.iUserId = 144 OR s.imStatusId = 4 OR (sub.iUserId = 853 AND sub.eStatus = 'Active')) GROUP BY r.vUrl ORDER BY recommendationDistance ASC LIMIT 0, 25