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.
r.fLat as rfLat,
r.fLon as rfLon,
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,
msy_recommendations AS r
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)
(r.eStatus = 'Active'
AND r.eRerecommend = 'Enable'
AND r.iCategoryId IN(3,5,6))
(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
I would double check that you have all your indexes in place. Every column that's part of a join. Every column that's part of a "where" clause. Every column that you group by. Every column that you order by. Also, ordering by a math formula that's calculated on the fly for each row can't be good. I'd suggest saving the pre-calculated value in its own indexed column.
That problem is that the formula is dynamic. It's based on the requesting users location, which can be anywhere in the world.