Optimization techniques for select query on single table with ~2.25M rows?

Question

I have a MySQL table running on the InnoDB engine called squares that has roughly 2,250,000 rows with the following table structure:

`squares` (
   `square_id` int(7) unsigned NOT NULL,
   `ref_coord_lat` double(8,6) NOT NULL,
   `ref_coord_long` double(9,6) NOT NULL,
   PRIMARY KEY (`square_id`),
   KEY `ref_coord_lat` (`ref_coord_lat`),
   KEY `ref_coord_long` (`ref_coord_long`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The first column square_id holds a simple incrementing value from 0 - 2.25M, while ref_coord_lat & ref_coord_long hold a set of latitude and longitude coordinates in decimal degrees for a point, respectively.

This is a read-only table. No additional rows will be added, and the only query which needs to be run against it is the following:

SELECT * FROM `squares` WHERE 
  `ref_coord_lat` BETWEEN :southLat AND :northLat AND 
  `ref_coord_long` BETWEEN :westLong AND :eastLong

...where the values following the colons are PHP PDO placeholders. Essentially, the goal of this query is to fetch all coordinate points in the table that are currently in the viewport of a Google Maps window which is bounded by the 4 coordinates in the query.

I've limited the zoom level where this query is run with the Google Maps API, so that the maximum amount of rows that can be fetched is ~5600. As the zoom level increases, the resultant fetch total decreases significantly.

Running such an example query directly in PHPMyAdmin takes 1.40-1.45 seconds. This is far too long. I'm already running standard indices on ref_coord_lat and ref_coord_long which brought the query time down from ~5 seconds, but this is still much too large for a map where an end user expects a timely response.

My question is simply: How can I further optimize this table/query to increase the speed at which results are fetched?

Answer 1

Creating compound index on (lat, long) should help a lot.

However, right solution is to take a look at MySQL spatial extensions. Spatial support was specifically created to deal with two-dimensional data and queries against such data. If you create appropriate spatial indexes, your typical query performance should easily exceed performance of compound index on (lat, long).