Your question is basically “What is a good index?”. Perhaps you should consider reading them in the MySQL documentation here on stackoverflow and just using any search engine.
Consider an index similar to the index in a large encyclopedia. Many topics are defined, so the index helps you find what you are looking for a little faster.
But what should be in the index? Category (science, entertainment, people, ...)? Then, when you find a category, there are still a lot of articles that fall into each category. Say that there are 10,000 articles, of which 1,000 are in the scientific category. If you are looking for something scientific, it still leaves you 1000 articles to view your exact article. In terms of the database, this index does not have good power : it is good if you have nothing else, but not specific enough to really speed things up. The same could be used for the index by the starting letter (26 letters in the alphabet, so using the index you divide the number of articles that need to be searched by about 26, which is also not very specific).
In databases, this means that the primary key is a very good field for indexing: one value of this field corresponds to exactly one value in the data, therefore, as soon as the index is used to find it, there is nothing left to see; You have already found a specific entry.
The truth / flag flag, on the other hand, divides only your data into a maximum of two groups, so it still leaves a lot of data to view even after using the index.
Of course, there are exceptions. For example, a table with a true / false column. This is usually a bad column for indexing. But perhaps you know that only 0.01% of all records will have a value of “true” for this column, and your query will look for true values, not false values. In this case, this true / false column is a good indexing column.
Then a range problem arises: you are not looking for a specific identifier, but a whole series of them, therefore, even if the identifier is unique, it will still mark the whole section of the index (and therefore the data) as “things to look after using the index " Thus, although it has good power , it may not be the best index to use for this particular query.
Another problem is that MySQL cannot view an index with multiple columns unless you are looking for the first column of the index. Thus, an index (ID, countrycode, status, flag_cc) will mean that MySQL should still start using the index by identifier, which is a range condition in your query, and the previous paragraph explains why this is bad. Only after applying the identification part of the index can it begin with part of the country code if MySQL determines what else is worth it. This is probably why MySQL wants to use your primary key index, even if you provide it with another option.
Applying all this information to your table: where clause contains all columns, so create an index starting with the highest power column (very different values) and which is not used as the range where the clause is indicated (so the ID ). If flag_cc contains many different values, use this. If status or countrycode contains even more different values, use one of them. Depending on how specific the first index you specified, indexing one column may be enough. If not, try adding a column with the next best power to the index, etc.
And, of course, remember that indexes (as a rule, not always) speed up the search, but slow down updates, insert and delete!
So, you see, this is not a very simple problem. Also think that the things that I have outlined are just the end of the iceberg indexing.
Sources:
http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/
https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html