How to do an efficient case insensitive search As an example, take a city table with 2 columns


city_ref (bigint)
city_name (varchar)


with indexes on both.

I want to do a case independent search by city_name.  


I know I can use like but that's not very efficient.
One way would be to store the Uppercase variation of the city_name in another field.  
But I can't help but think that there should be a way to use charset or collation to do this.


Can someone point me in the right direction please.
There is no problem using LIKE, as long as it's in a format such as:

SELECT * FROM My_Table WHERE City_Name LIKE 'Abc%';

This can use an index. However, the following cannot.

SELECT * FROM My_Table WHERE City_Name LIKE UPPER('Abc%');

The use of a function (UPPER()) negates the index - in technical terms, the query is not sargable. Note that the query is also non-sargable in a case such as:

SELECT * FROM My_Table WHERE City_Name LIKE  '%Abc..';

The wild card at the beginning of the search string renders any index non-functional. 

An ON INSERT and and ON UPDATE trigger into a new field (up_City_Name) setting the input City_Name to UPPER(input City_Name). 

That way, you will always know that your City_Name is in upper case, and your server will always be able to use an index, provided you search on the new field up_City_Name.