0

So I first made a bounding box and then get the coordinated in that box. The problem is the query still takes over 3 seconds to complete (20,000 entries in the table). I have latitude, longitude and deleted as indexes. I can't figure out what else can be optimized. I know I haven't implemented PDO yet but that's a different story.

I'm thinking it's the json_encode function that's taking too long:

<?php
include 'connection.php';
error_reporting(0);

if($_GET["lat"]){
    $radius = $_GET["radius"];
    $maxLat = $_GET["lat"] + rad2deg($radius/3959);
    $minLat = $_GET["lat"] - rad2deg($radius/3959);
    $maxLon = $_GET["long"] + rad2deg(asin($radius/3959) / cos(deg2rad($_GET["lat"])));
    $minLon = $_GET["long"] - rad2deg(asin($radius/3959) / cos(deg2rad($_GET["lat"])));

    //Query to get events within specified distance
    $qry = @"SELECT `Posts`,`Votes`,`Date`,`Latitude`,`Longitude`,`PostID`,`Comments`, 
            `Picture`, `City`, `DeviceID`, (((acos(sin((".$_GET["lat"]."*pi()/180)) * 
            sin((`latitude`*pi()/180))+cos((".$_GET["lat"]."*pi()/180)) * 
            cos((`latitude`*pi()/180)) * cos(((".$_GET["long"]."- `longitude`)*pi()
            /180))))*180/pi())*60*1.1515) as distance FROM `TABLE` WHERE `deleted`= 0 AND 
            `latitude` Between $minLat AND $maxLat AND `longitude` Between $minLon AND 
            $maxLon HAVING distance <= $radius ORDER BY `PostID` DESC LIMIT 60 " ;
    $result = mysql_query($qry);

    if (!$result) {
        die("Query to show fields from table failed");
    }

    $json = array();

    $json[] = array('Posts' => $row[0], 
                    'Votes' => $row[1], 
                    'Date' => $row[2], 
                    'Latitude' => $row[3], 
                    'Longitude' => $row[4], 
                    'PostID' => $row[5], 
                    'comments' => $row[6], 
                    'canvote' => $id, 
                    'picture' => $row[7], 
                    'city' => $row[8], 
                    'DeviceID' => $row[9]);

    echo json_encode($json);
else {
    echo 'Error';
}
?>
reformed
  • 4,205
  • 10
  • 58
  • 83
Pablo Escobar
  • 111
  • 1
  • 5
  • you can get some good insights **[at this duplicate question](https://stackoverflow.com/questions/1813460/database-best-performance-way-to-query-geo-location-data)** ... read the articles in the answers. A start point. – YvesLeBorg Sep 06 '17 at 00:01
  • Possible duplicate of [Database: Best performance way to query geo location data?](https://stackoverflow.com/questions/1813460/database-best-performance-way-to-query-geo-location-data) – YvesLeBorg Sep 06 '17 at 00:01
  • Did you watch the request in the network tab in console (at least in Chrome) ? That should make it quite clear what takes how long. In terms of differentiating between elaboration and transfer time that is of course. – deg Sep 06 '17 at 00:11

0 Answers0