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.
This topic is now closed. New replies are no longer allowed.