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