Distance Computation...chp 3

[Duke]

I am using a variation of the distance computation in Chp 3.... I do not have a large amount of data in the mySqli database... The query takes a very long time to run....I found it has to do with calculating the distance in the query...Was wondering if anyone else had this issue or has any thoughts...My query>>>

 

$sr="SELECT l.load_id, CONVERT_TZ(l.date_posted,'UTC','$tz') AS date_posted,

                DATE_FORMAT(l.date_available, '%m/%d/%y') AS date_available ,

                e.equipment_type,e.equipment_size,e.weight,e.hazmat,e.liquor,e.bonded, e.xtra_pickup,e.xtra_delivery,

                o.city_origin, o.state_origin,o.zip_origin,

                d.city_destination,d.state_destination,d.zip_destination,

                l.shipper_id, l.comment, l.pickup_time, l.delivery_day, l.delivery_time, l.rate,l.commodity,

                s.company_name, s.broker_mc

, 

ROUND(DEGREES(ACOS(SIN(RADIANS('$lat_origin')) 

* SIN(RADIANS(latitude_origin)) 

+ COS(RADIANS('$lat_origin'))  

* COS(RADIANS(latitude_origin))

* COS(RADIANS('$long_origin' - longitude_origin)))) * 69.09) 

 AS distance_origin 

 

 , 

ROUND(DEGREES(ACOS(SIN(RADIANS('$lat_destination')) 

* SIN(RADIANS(latitude_destination)) 

+ COS(RADIANS('$lat_destination'))  

* COS(RADIANS(latitude_destination))

* COS(RADIANS('$long_destination' - longitude_destination)))) * 69.09) 

 AS distance_destination

 

 

 FROM  loads AS l

 INNER JOIN origin AS o USING(load_id) 

 INNER JOIN destination AS d USING(load_id)

 INNER JOIN equipment AS e USING(load_id)

 INNER JOIN shippers AS s

 

 WHERE 

 s.shipper_id = l.shipper_id 

 

 AND

 $search_for ";....<<<this varies as to what fields the users wants to search..I use

 if(  (empty($_POST['company_name']))....to select the appropriate $search_for query....

 

 

Many thanks

 

Update...I just emptied the tables and reloaded new data....some of my lat and long fields had zeros as I added these fields recently......I only have about 8 columns in now and is quering much faster.......

[HartleySan]

Have you tried moving the distance calculations over to the PHP side, and seeing how things work out?

I'm not sure that'll be faster, but it's worth a try.

 

Really though, this might be a good case for attempting to use an optimized third-party API.

Just a couple of thoughts.

[Duke]

Hey thanks for the tips....It seems to be faster-may have been my data...if it slows again..I will try moving distance calc. to PHP side...also just made it a little more complicated as I added another query to find records within the found set of first query and not display the found records.....tried to used one large query for both..just not able to get it going....