How to speed up this simple mysql query?

The query is simple:

SELECT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*cos(-6.185*0.017453292519943)*cos(TB.Latitude*0.017453292519943)) AS Distance
FROM
  `tablebusiness` AS TB
WHERE
  -6.2767668133836 5   AND 106.68123318662 < TB.Longitude AND TB.Longitude <106.86476681338
ORDER BY
  Distance

See, we just look at all business within a rectangle. 1.6 million rows. Within that small rectangle there are only 67,565 businesses.

The structure of the table is

```
1 ID varchar(250) utf8_unicode_ci No None Change Change Drop Drop More Show more actions
2 Email varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
3 InBuildingAddress varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
4 Price int(10) Yes NULL Change Change Drop Drop More Show more actions
5 Street varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
6 Title varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
7 Website varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
8 Zip varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
9 Rating Star double Yes NULL Change Change Drop Drop More Show more actions
10 Rating Weight double Yes NULL Change Change Drop Drop More Show more actions
11 Latitude double Yes NULL Change Change Drop Drop More Show more actions
12 Longitude double Yes NULL Change Change Drop Drop More Show more actions
13 Building varchar(200) utf8_unicode_ci Ye

I've only given this a little thought, it seems like you are scanning the whole table (which involves reading much extraneous information). I'd try creating a separate geo table containing the LAT/Long info and precalculate the Distance expression on insertion, index the Distance column, do your search on the geo table and reference the main table only when you need information from it.

StackExchange Database Administrators Q#18829, answer score: 2