4

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)

Explain SQL shows this:

1   SIMPLE  TB  ALL     Latitude,Longitude  NULL    NULL    NULL    1642681     Using where; Using filesort

Update: Not computing distance takes only .0113 seconds! I do not "really" need the distance. I only need things like 20 or 100 closest match.

MySQL does not contain GEO feature I think. Or maybe it's what I am missing. I host at hostgator. The most famous (and best) hosting company in the world.

0

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.