0

Based on http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ The following SQL Query returns an array of cities nearest to $lat/$lon pair within a given $radius. I added another join-statement (c.) to display the code3l-column from table Countryfor each city. Overall this statement takes about 1.2sek due to the amount of 3Mio entries 

SELECT population, AccentCity, Country, code3l, name, City, Region,
       Latitude, Longitude, distance, id          
      FROM (
         SELECT z.population,z.id,
                z.AccentCity,z.Country, z.City, z.Region,
                z.Latitude, z.Longitude,
                c.name, c.code3l,
                p.radius,
                p.distance_unit
                         * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                         * COS(RADIANS(z.latitude))
                         * COS(RADIANS(p.longpoint - z.longitude))
                         + SIN(RADIANS(p.latpoint))
                         * SIN(RADIANS(z.latitude)))) AS distance

          FROM worldpopulation AS z
          JOIN (   
                SELECT  '.$lat.'  AS latpoint,  '.$lon.' AS longpoint,
                        '.$radius.' AS radius,      111.045 AS distance_unit
            ) AS p ON 1=1

          JOIN (   
                SELECT * FROM countries
            ) AS c ON c.code2l = z.Country


          WHERE 
            z.population IS NOT NULL 

            AND z.latitude
             BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                 AND p.latpoint  + (p.radius / p.distance_unit)
            AND z.longitude
             BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                 AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         ) AS d
     WHERE distance <= radius
     ORDER BY distance ASC';

In addition to that I need to add a some count(*)-statements to return the amount of passengers in each city. So I tried to add these like this:

SELECT population, AccentCity, Country, code3l, name, City, Region,
       Latitude, Longitude, distance,id,
       (select count(*) from passengers where city_id = d.id) AS passenger,
       (select count(*) from passengers where city_id = d.id AND
                                 start = "'.$icao.'") AS passengerFromHere,
       (select count(*) from passengers where city_id = d.id AND
                             destination = "'.$icao.'") AS passengerToHere
FROM (
         SELECT z.population,z.id,
...

but the table named passengers is growing fast, and with that the overall executing time for the statement

I there a way to Join the heavy passengers table, having the same result (array of cities with their respective passengers) in a better executing time

TNT_Larsn
  • 73
  • 8
  • try using `count(id)` instead of `count(*)` as it will select everything in the table. you should always avoid using *. – Alexander Teitelboym Jan 11 '16 at 18:49
  • Performance deteriorates with volume - a clear sign that you need good hardware which can support the growth of your data. There's only so much you can do until you hit the limitation of one of your devices. Now, you can try to hack and slash and what not, but in the end - even the best code or query ever hits that hardware limit. – N.B. Jan 11 '16 at 19:09
  • found this interesting article: [link](http://www.howtogeek.com/howto/programming/speed-up-your-web-site-with-mysql-query-caching/). try playing with these values in the config file: `query_cache_size = 268435456` `query_cache_type=1` `query_cache_limit=1048576` – Alexander Teitelboym Jan 11 '16 at 21:38
  • **@AlexanderTeitelboym** Note: _italic_ if your application updates tables frequently, then the query cache will be constantly purged and you won’t get much or any benefit from this" unfortunately the passengers table will update frequently. Thanks for the good reading anyway – TNT_Larsn Jan 11 '16 at 21:48

1 Answers1

0

I took out the Passenger count-statement from SQL. Built a php function that loops thru each city and adds the passenger count to the city, if the key with name of city_id exists

if(isset($cityArray[$passengers[$key[$i]]->city_id]))

As replacement for the sql statement:

(select count(*) from passengers where city_id = d.id AND
                             start = "'.$icao.'") AS passengerFromHere,

the codeigniter function, that I came up with, is this:

public function add_PassengersFromHereToCitys($icao, $cityArray){

            $query = $this->db->select('count(city_id) as passengers, city_id')->from('passengers')->where('start', $icao)->group_by('city_id')->get();
            $passengers = $query->result();

            $key = array_keys($passengers);
            $size = sizeOf($key);
            for ($i=0; $i<$size; $i++){

                if(isset($cityArray[$passengers[$key[$i]]->city_id])){
                    $cityArray[$passengers[$key[$i]]->city_id]->passengerFromHere = $passengers[$key[$i]]->passengers;
                }

            }  

            return $cityArray;
        }

Have done this with the 2nd count(*) respectively.

Now with this php loop, getting the desired results is way faster than pulling the date inside the sql-statement. However, If anyone has an idea to modify the sql statement. Let me know!

TNT_Larsn
  • 73
  • 8