SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    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. :|

    Code:
    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

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,278
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    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.
    "First make it work. Then make it better."

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •