Slow Query

[agriz]

Hi

There are two tables.
Cities and Countries

Countries have Id, Country name and Short Code (Id is primary key)
Cities have Country_Short_Code, City and Populations. (city is indexed)

There are 5000 records maximum to be processed.
PHP will give 5000 city name and its country.

What is the best way to write a query?

select population from cities inner join countries where (country_name = 'cname' and city = 'city') or (country_name = 'cname' and city = 'city') or ( .. repeating for 5000 times)

To get those 5000 records in single query. It is slower.

OR

foreach($locations as $loc) { 
select population from cities inner join countries where (country_name = 'cname' and city = 'city') 
}

5000 different queries on foreach loop.

Which is the best way to achieve this?

[hericles]

Maybe I'm not fully understanding the question but a list of all of the cities ordered by country could be done like this:

SELECT Country, City, Populations FROM Countries INNER JOIN Cities ON Countries.Short_code = Cities.Country_Short_Code ORDER BY Country, City

Assuming short_code and countries_short_code are the same thing in both tables.

[agriz]

ON Countries.Short_code = Cities.Country_Short_Code

I forgot to mention this. I added this line. Still the query is very slow.
Cities table have 2,797,245 records in total.

[hericles]

OK, is it indexed? Indexes can really help speed up large searches.

[agriz]

Total Data In cities table is 2,797,245 Records

CREATE TABLE IF NOT EXISTS `cities` (
`Country` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`City` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`AccentCity` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Region` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Population` int(10) unsigned NOT NULL,
`Latitude` float NOT NULL,
`Longitude` float NOT NULL,
KEY `AccentCity` (`AccentCity`(5)),
KEY `City` (`City`(5))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

countries table has the following structure with some 286 records

CREATE TABLE IF NOT EXISTS `country` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`short_code` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

City is indexed in cities table
But still it is very slow. In localhost, It took more than 3 sec. Mysqld took 100% CPU usage.

[agriz]

Is it good idea to loop the query inside the foreach loop for 5000 times with only one condition?

[urtrivedi]

If possible then you must refer to country id rather than country code, I means I think numerice referece will perform better than varchar reference.

If that is not possible then, Also create key for short_code in both table (country and city table)

[agriz]

Do you mean Numeric Key for "short_code" in city table and make it foreign key?
Is it possible to do any update for the above structures?

Also, PHP will give only Country Full Name :( Data is coming from partner website.
If update is possible, I will do inner join "ON country.id = city.country_id" WHERE (name = 'country name' AND City = 'city name') OR (name = 'country name' AND City = 'city name') ...

[smantscheff]

Create a temporary table (country_id, city_name), load your 5.000 queried records into it and then form a join between this temporary table and the big cities table.

[agriz]

Create a temporary table (country_id, city_name), load your 5.000 queried records into it and then form a join between this temporary table and the big cities table.

Joining them with city_name?
I am confused. How do i get country_id?

I am getting city name and country name from script.