GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
How to improve query performance for geonames  XML
Forum Index -> FAQ - frequently asked questions
Author Message
Erica Zhang



Joined: 19/07/2007 06:39:00
Messages: 12
Offline

Hi,

I download and import geonames file, including allcountries.txt into the mysql. However I found it is very slow to make the query. Then I use index. Still very slow. I do not know what should I do. I wonder if somebody here could give me some suggestions on it ?

I am doing an experiment on the performance of web application. We want to know the effect caused by network latency, so we want to know the effect caused by database performance.

Thanks a lot for your help,

Jun
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

Hi Jun

I fear it will be difficult to help you if we don't know which query you want to execute and which indices you have created.
If you post the information someone might be able to help.

Cheers

Marc

[WWW]
Erica Zhang



Joined: 19/07/2007 06:39:00
Messages: 12
Offline

Well, I just executed the following script and import the tables and use indices.

CREATE TABLE IF NOT EXISTS alternatename (
alternateNameId INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT
'the id of this alternate name',
geonameid INT(10) NULL COMMENT 'geonameId referring to id in table geoname',
isolanguage VARCHAR(4) NULL COMMENT 'iso 693 language code 2- or
3-characters; 4-characters post for postal codes and iata or icao for
airport codes',
alternate_name VARCHAR(200) NULL COMMENT 'alternate name or name variant',
isPreferredName INT(1) NULL COMMENT '1 if this alternate name is an
official/preferred name',
isShortName INT(1) NULL COMMENT '1, if this is a short name like
California for (State of California)'
);


CREATE TABLE IF NOT EXISTS continentcode (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the
id of continents in geoname table',
name VARCHAR(100) NULL COMMENT 'name of continent',
code VARCHAR(2) NULL COMMENT 'code of continent'
);


REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255146', 'Africa', 'AF');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255147', 'Asia', 'AS');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255148', 'Europa', 'EU');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255149', 'North America', 'NA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255151', 'Oceania', 'OC');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255150', 'South America', 'SA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255152', 'Antarctica', 'AN');


CREATE TABLE IF NOT EXISTS admincodes1 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL
);


CREATE TABLE IF NOT EXISTS admincodes2 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
name2 VARCHAR(100) NULL,
geonameid INT(10) NULL
);


CREATE TABLE IF NOT EXISTS countryinfo (
iso_alpha2 CHAR(2) NULL, INDEX (iso_alpha2),
iso_alpha3 CHAR(3) NULL,
iso_numeric INT(10) NULL,
fips CHAR(3) NULL,
name VARCHAR(200) NULL, INDEX (name),
capital VARCHAR(200) NULL,
areainsqkm DECIMAL(20,1) NULL,
population INT(20) NULL,
continent CHAR(2) NULL,
languages VARCHAR(200) NULL,
other1 VARCHAR(3) NULL,
other2 INT(20) NULL
);



LOAD DATA INFILE '/export/home/thesis_back/projects/countryInfo.txt'
REPLACE INTO TABLE countryinfo FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/export/home/thesis_back/projects/admin1Codes.txt'
REPLACE INTO TABLE admincodes1 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/export/home/thesis_back/projects/admin2Codes.txt'
REPLACE INTO TABLE admincodes2 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE
'/export/home/thesis_back/projects/alternateNames.txt' REPLACE INTO
TABLE alternatename FIELDS TERMINATED BY '\t';


Then I want to execute following commands :

sqlstr="select count(distinct country_code, admin1_code) from geoname
where latitude<='" + w + "' AND latitude>='" + y + "' AND
longitude<='" + z + "' AND longitude>='" + x +"'";

....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,c.name from geoname g
LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code where
country_code='" + country + "' AND admin1_code='" + area + "' AND
feature_class='P' AND feature_code='PPLC'" +" LIMIT 0,"+
String.valueOf(percount);
.....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,g.feature_code,c.name
from geoname g LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code
where country_code='" + country + "' AND admin1_code='" + area + "'
AND feature_class='P' AND (feature_code='PPLC' OR
feature_code='PPLA')" +" LIMIT 0,"+ String.valueOf(percount);
....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,g.feature_code,c.name
from geoname g LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code
where country_code='" + country + "' AND admin1_code='" + area + "'
AND feature_class='P' AND (feature_code='PPLA' OR feature_code='PPL')"
+" LIMIT 0,"+ String.valueOf(percount);
.....

I use MySQL 5 . I am new to database. I hope you or somebody could help me to improve the performance.

Regards,

Erica

marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

Hi Erica

Do you have indices on the geoname table? In particular on latitude and longitude?

Marc

[WWW]
Erica Zhang



Joined: 19/07/2007 06:39:00
Messages: 12
Offline

Sorry to reply this email so late.

Yes, I have indices for latitude and longitude.
 
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team