Hi,

I have created a table which holds around 3 million world city records.

CREATE TABLE `worldcity` (
`ID` int(11) unsigned NOT NULL DEFAULT '0',
`AreaName` varchar(100) NOT NULL,
`Lat` double(8,4) NOT NULL,
`Lon` double(8,4) NOT NULL,
`ParentID` mediumint(9) NOT NULL,
`RootID` mediumint(9) NOT NULL,
`Pop` varchar(30) DEFAULT NULL,
`PopInt` int(10) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `Index_3` (`ParentID`),
KEY `Index_4` (`RootID`),
KEY `Index_2` (`Lat`),
KEY `Index_6` (`AreaName`),
KEY `Index_7` (`Lon`),
KEY `Index_5` (`PopInt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The issue which I am facing is if I run a simple query like this to get all the cities within a country then it is taking around 5 seconds to execute.

SELECT wc.*, (SELECT AreaName from worldcity where ID = wc.RootID) as 'Country' from worldcity wc Where (RootID=1) Order By PopInt desc Limit 0, 20;

I don't have much experience in SQL and would really appreciate if someone could guide me where I am doing wrong.

Many Thanks

Why do you have ID defined as unsigned int(11) but RootID as mediumint(9). There will be some form of conversion here which will slow down the query and may even result in the index not being picked up. Keep all the related data types the same. Also I would change the query as follows:

SELECT wc.*, b.AreaName as 'Country'
FROM worldcity wc,
worldcity b
Where wc.RootID=1
AND b.ID = wc.RootID
Order By wc.PopInt desc
Limit 0, 20;

Thanks for your answer. I will make the change and see if that helps.