Duke Posted March 18, 2014 Share Posted March 18, 2014 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....... Link to comment Share on other sites More sharing options...
HartleySan Posted March 18, 2014 Share Posted March 18, 2014 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. Link to comment Share on other sites More sharing options...
Duke Posted March 19, 2014 Author Share Posted March 19, 2014 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.... Link to comment Share on other sites More sharing options...
Recommended Posts