title: MySQL query optimization with complex index
body: <p>I have a database used for simple reverse geocoding. The database rely on a table containing latitude, longitude and place name. Everytime a couple latitude,longitude is not present or, better, everytime the searched latitude,longitude differs too much from an existing latitude, longitude, I add a new row using GoogleMaps reverse geocoding service. 
  Below the code to generate the address table:</p>
  
  <pre><code>CREATE TABLE `data_addresses` (
      `ID` int(11) NOT NULL COMMENT 'Primary Key',
      `LAT` int(11) NOT NULL COMMENT 'Latitude x 10000',
      `LNG` int(11) NOT NULL COMMENT 'Longitude x 10000',
      `ADDRESS` varchar(128) NOT NULL COMMENT 'Reverse Geocoded Street Address'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  ALTER TABLE `data_addresses`
      ADD PRIMARY KEY (`ID`),
      ADD UNIQUE KEY `IDX_ADDRESS_UNIQUE_LATLNG` (`LAT`,`LNG`),
      ADD KEY `IDX_ADDRESS_LAT` (`LAT`),
      ADD KEY `IDX_ADDRESS_LNG` (`LNG`);
  ALTER TABLE `data_addresses`
      MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key';
  </code></pre>
  
  <p>As you can see the trick is to use place two indexes on Latitude and Longitude. As normally latitude and longitude are float we use their value multiplied by 10000, so each couple latitude/longitude is unique. This implies a resolution of about 50m that is satisfying for my needs.</p>
  
  <p>Now the problem: everytime I need to know if a given latitude/longitude (MyLat,MyLon) is already present or not I execute the following query:</p>
  
  <pre><code>SELECT `id`, ROUND(SQRT(POW(ABS(`LAT`-ROUND(MyLat*10000)),2)+POW(ABS(`LNG`-ROUND(MyLon*10000)),2))) AS R FROM splc_smarttrk.`data_addresses` ORDER BY R ASC LIMIT 1
  </code></pre>
  
  <p>This query will return to me the closest point and will give me also R (the rating): smaller R means closest approximation, so let say that everytime I find an R that is above 10 I need to add a new row to address table.
  Address table at present contains about 615k rows.</p>
  
  <p>The problem is that despite indexes that I have placed this query is too slow (takes about 2 seconds on a 2x Xeon server). Below the results of Explain:</p>
  
  <p><a href="https://i.stack.imgur.com/n2fEp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/n2fEp.png" alt="enter image description here"></a></p>