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?

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.

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.

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

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.

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

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)

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') ...

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.

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.