Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Can you run an explain plan and post the results please? –  maple_shaft Jun 5 '12 at 11:05
1  
And what is this: FoursquarePeopleCount >5 ? I see no such column in the definition. –  ypercube Jun 5 '12 at 11:07
1  
innodb 1 SIMPLE TB ALL Latitude,Longitude NULL NULL NULL 1642681 Using where; Using filesort –  Jim Thio Jun 5 '12 at 11:10
1  
@EmmadKareem The cos expression is in the select meaning it will only run 67,565 times in this query. I can't imagine that this would impact the query more than a couple hundred milliseconds though... –  maple_shaft Jun 5 '12 at 12:57
1  
Don't waste time with the cos thing. This is not what makes the query slow. It's scanning the whole 1.6M rows of the table because the query planner sees no good index for it. –  ypercube Jun 5 '12 at 13:50
show 6 more comments

migrated from programmers.stackexchange.com Jun 5 '12 at 11:03

This question came from our site for professional programmers interested in conceptual questions about software development.

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
1  
And then, when he needs to query with other coordinates, what should the OP do? –  ypercube Jun 5 '12 at 14:04
    
That's not what he implied, if he needs other coordinates then precalculating the distance is pointless. Separating out the geo information to reduce the amount of row data read is still likely to provide speedups. –  epo Jun 5 '12 at 14:12
    
He has a query that searches for businesses inside a rectangle. I don't think he needs to imply that he may be searching in various areas. –  ypercube Jun 5 '12 at 14:23
    
I wonder if mysql correctly search only businesses within that rectangle (65k). Also I wonder if there is a way so I can get closest 20 without having to compute all those distance. –  Jim Thio Jun 5 '12 at 14:29
    
That's right do the computation once into a table and forget about it. It's not like the distance beween the various spots onteh earth will change. –  HLGEM Jun 5 '12 at 19:14
show 3 more comments

Find nearest, given lat/lng: http://mysql.rjweb.org/doc.php/latlng

share|improve this answer
    
But it is a solution. And you can't do much to "speed up the simple query" with out drastic steps. –  Rick James Sep 13 '12 at 0:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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