title: Optimize a MySQL count each duplicate Query
body: <p>I have the following query That gets the city name, city id, the region name, and a count of duplicate names for that record:</p>
  
  <pre>
  SELECT Country_CA.City AS currentCity, Country_CA.CityID, globe_region.region_name,
  ( SELECT count(Country_CA.City) FROM Country_CA WHERE City LIKE currentCity ) as counter 
      FROM Country_CA
      LEFT JOIN globe_region
        ON globe_region.region_id = Country_CA.RegionID
        AND globe_region.country_code = Country_CA.CountryCode
      ORDER BY City
  </pre>
  
  <p>This example is for Canada, and the cities will be displayed on a dropdown list. </p>
  
  <p>There are a few towns in Canada, and in other countries, that have the same names. Therefore I want to know if there is more than one town with the same name region name will be appended to the town name. Region names are found in the globe_region table. </p>
  
  <p>Country_CA and globe_region look similar to this (I have changed a few things for visualization purposes)</p>
  
  <pre>
  CREATE TABLE IF NOT EXISTS `Country_CA` (
    `City` varchar(75) NOT NULL DEFAULT '',
    `RegionID` varchar(10) NOT NULL DEFAULT '',
    `CountryCode` varchar(10) NOT NULL DEFAULT '',
    `CityID` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`City`,`RegionID`),
    KEY `CityID` (`CityID`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  </pre>
  
  <p>AND</p>
  
  <pre>
  CREATE TABLE IF NOT EXISTS `globe_region` (
    `country_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
    `region_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
    `region_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`country_code`,`region_code`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  </pre>
  
  <p><STRONG>
  The query on the top does exactly what I want it to do, but It takes way too long to generate a list for 5000 records. I would like to know if there is a way to optimize the sub-query in order to obtain the same results faster.
  </STRONG></p>
  
  <p>the results should look like this</p>
  
  <pre>
  <strong>
  City            CityID          region_name       counter
  </strong>
  sheraton        2349269         British Columbia   1
  sherbrooke      2349270         Quebec             2
  sherbrooke      2349271         Nova Scotia        2
  shere           2349273         British Columbia   1
  sherridon       2349274         Manitoba           1
  </pre>