My table currently looks like this:
CREATE TABLE IF NOT EXISTS `x_geodata` (
`post_id` int(11) NOT NULL,
`post_type` varchar(20) NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
How to index this table correctly and efficiently?
The only query that runs in this table is as follows:
if(!empty($_SESSION['s_property_radius'])) {$dist = $_SESSION['s_property_radius'];}else{$dist = 50;}
$orig_lat = $_SESSION['s_property_address_lat'];
$orig_lon = $_SESSION['s_property_address_lng'];
$lon1 = $orig_lon - $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lon2 = $orig_lon + $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lat1 = $orig_lat - ( $dist / 69 );
$lat2 = $orig_lat + ( $dist / 69 );
$sql = "
SELECT `t`.`post_id`, 3956 * 2 * ASIN( SQRT( POWER( SIN( ( ".$orig_lat." - `t`.`lat` ) * pi() / 180 / 2), 2 ) + COS( ".$orig_lat." * pi() / 180) * COS( `t`.`lat` * pi() / 180 ) * POWER( SIN( ( ".$orig_lon." - `t`.`lng` ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` FROM (
SELECT `post_id`, `lat`, `lng` FROM `x_geodata` WHERE `post_type` = 'some post type' AND `lng` BETWEEN '".$lon1."' AND '".$lon2."' AND `lat` BETWEEN '".$lat1."' AND '".$lat2."'
) AS `t` HAVING `distance` <= ".$dist."
";
The request is checked to make sure that we are looking at the correct message type, and then we will check the square radius on lat and lng. The results are then performed by checking the circular radius.
What I'm looking for is updating CREATE TABLE SQL or UPDATE TABLE SQL to get it INDEXED correctly.
Any help is greatly appreciated.
EDIT: Was there EXPLAIN in my request based on arnep response, and I got the following:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
2 DERIVED zch_geodatastore range post_type post_type 70 NULL 3 Using where
I don't know what that means, though ...