Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2015
    Posts
    3

    Answered: Improve/optimize MySQL code

    Is there any way to optimize this code to run faster and do not execute the same thing twice? (I do have indexes set on users.lat and users.lng)

    Code:
    SELECT COUNT(*) AS count FROM users WHERE users.activated = '1' AND users.id != '1'
    
    AND ROUND((DEGREES(ACOS(SIN(RADIANS('$c_lat')) * SIN(RADIANS(users.lat)) + COS(RADIANS('$c_lat')) * COS(RADIANS(users.lat)) * COS(RADIANS('$c_lng' - users.lng))))) * 60 * 1.1515 * 1.60934 * 1000) >= 1000
    
    AND ROUND((DEGREES(ACOS(SIN(RADIANS('$c_lat')) * SIN(RADIANS(users.lat)) + COS(RADIANS('$c_lat')) * COS(RADIANS(users.lat)) * COS(RADIANS('$c_lng' - users.lng))))) * 60 * 1.1515 * 1.60934 * 1000) <= 5000
    This code gets the count of all users who are between a distance of 1000 meters and 5000 meters from $c_lat and $c_lng that I pass in PHP.

    I also have something like this.. so I need to be able to select it as well:

    Code:
    SELECT ROUND((DEGREES(ACOS(SIN(RADIANS('$c_lat')) * SIN(RADIANS(users.lat)) + COS(RADIANS('$c_lat')) * COS(RADIANS(users.lat)) * COS(RADIANS('$c_lng' - users.lng))))) * 60 * 1.1515 * 1.60934 * 1000) AS distance FROM users WHERE users.activated = '1' AND users.id != '1' 
    
    AND ROUND((DEGREES(ACOS(SIN(RADIANS('$c_lat')) * SIN(RADIANS(users.lat)) + COS(RADIANS('$c_lat')) * COS(RADIANS(users.lat)) * COS(RADIANS('$c_lng' - users.lng))))) * 60 * 1.1515 * 1.60934 * 1000) >= 1000
    
    AND ROUND((DEGREES(ACOS(SIN(RADIANS('$c_lat')) * SIN(RADIANS(users.lat)) + COS(RADIANS('$c_lat')) * COS(RADIANS(users.lat)) * COS(RADIANS('$c_lng' - users.lng))))) * 60 * 1.1515 * 1.60934 * 1000) <= 5000
    I can't use: AND distance >= 1000 AND distance <= 5000

    Also I'd like to avoid using a HAVING clause.

    Thanks!

  2. Best Answer
    Posted by healdem

    "nope, not that I can see, you maybe bael to use the geographical/spatial extensions which will be better optimised that doing the trig inside the query."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Rather than do all that calculation inside a query what you could consider doing is calcilate he bounding noc in PHP and then match on lat long rather than cslculate a distance gor each row. However that approach will require a more complex where or having clause if the boinding box spans prime meridian or int date line.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Aug 2015
    Posts
    3
    Quote Originally Posted by healdem View Post
    Rather than do all that calculation inside a query what you could consider doing is calcilate he bounding noc in PHP and then match on lat long rather than cslculate a distance gor each row. However that approach will require a more complex where or having clause if the boinding box spans prime meridian or int date line.
    Hmm, I see, but I'd like to stick with the calculation inside the query, the only thing that bothers me is that I'm doing the same calculation more than one time, so is there a way in MySQL to just do it once, select it, then use it in the WHERE?

    Thanks!

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope, not that I can see, you maybe bael to use the geographical/spatial extensions which will be better optimised that doing the trig inside the query.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Aug 2015
    Posts
    3
    Quote Originally Posted by healdem View Post
    nope, not that I can see, you maybe bael to use the geographical/spatial extensions which will be better optimised that doing the trig inside the query.
    Okay, thanks! I will look into the spatial extensions then.

Posting Permissions

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