Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unhappy Unanswered: Simple query taking a long time

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Quote Originally Posted by it-iss.com View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •