How to speed up this simple mysql query?
- by Jim Thio
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 < TB.Latitude AND TB.Latitude < -6.0932331866164
AND FoursquarePeopleCount >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 Yes NULL Change Change Drop Drop More Show more actions
14 City varchar(100) utf8_unicode_ci No None Change Change Drop Drop More Show more actions
15 OpeningHour varchar(400) utf8_unicode_ci Yes NULL Change Change Drop Drop More Show more actions
16 TimeStamp timestamp on update CURRENT_TIMESTAMP No CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Change Change Drop Drop More Show more actions
17 CountViews int(11) Yes NULL Change Change Drop Drop More Show more actions
The indexes are:
Edit Edit Drop Drop PRIMARY BTREE Yes No ID 1965990 A
Edit Edit Drop Drop City BTREE No No City 131066 A
Edit Edit Drop Drop Building BTREE No No Building 21 A YES
Edit Edit Drop Drop OpeningHour BTREE No No OpeningHour (255) 21 A YES
Edit Edit Drop Drop Email BTREE No No Email (255) 21 A YES
Edit Edit Drop Drop InBuildingAddress BTREE No No InBuildingAddress (255) 21 A YES
Edit Edit Drop Drop Price BTREE No No Price 21 A YES
Edit Edit Drop Drop Street BTREE No No Street (255) 982995 A YES
Edit Edit Drop Drop Title BTREE No No Title (255) 1965990 A YES
Edit Edit Drop Drop Website BTREE No No Website (255) 491497 A YES
Edit Edit Drop Drop Zip BTREE No No Zip (255) 178726 A YES
Edit Edit Drop Drop Rating Star BTREE No No Rating Star 21 A YES
Edit Edit Drop Drop Rating Weight BTREE No No Rating Weight 21 A YES
Edit Edit Drop Drop Latitude BTREE No No Latitude 1965990 A YES
Edit Edit Drop Drop Longitude BTREE No No Longitude 1965990 A YES
The query took forever. I think there has to be something wrong there.
Showing rows 0 - 29 ( 67,565 total, Query took 12.4767 sec)
