Query taking >150 seconds to execute ... Need some optimization help

[Takai]

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

[Jeff Mott]

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.

[Takai]

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.