GeoNames Forum

Hello guys,

I've uploaded big geonames database to my server (MySQL). I wonder how can I get faster queries?

I have cheap server. It's Celeron 2.8 with 1 Gb of RAM. For example, this query takes 10 seconds:

SELECT * FROM geonamesbig WHERE latitude > '48.832633619561896' AND latitude < '48.90083790234088' AND longitude > '2.1649932861328125' AND longitude < '2.5014495849609375' ORDER BY 'population' DESC LIMIT 0, 20

I need to get fast queries, like 0.1-0.5 seconds instead of 10 seconds. What is the best way to get fast queries:

1. Better, more expensive server. Will this solve the problem? Or it's not worth of the money?

2. Optimizing queries. Still I don't think this will give me 0.1 seconds query instead of 10 seconds. Am I wrong?

3. Splitting database to 500-5000 tables by coordinates (splitting map virtually to small quares). Is this a good idea?

Any help would be appreciated.

Do you use any indices on your columns? You should also look at the geospatial features of mysql :
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

We at GeoNames are using Postgres/postgis.

Cheers

Marc

marc wrote:

Do you use any indices on your columns? You should also look at the geospatial features of mysql :
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

We at GeoNames are using Postgres/postgis.

Cheers

Marc

Thanks Marc, I will defenetly read this.