MySQL: Collate in query - any side effects?

Question

My OpenCart table collation is utf8_bin, unfortunately I can't search for product names with accent in their name. I searched on Google and just found that the collation must be utf8_general_ci for accent compatible and case insensitive search.

What If I add collate declaration to the search query?

SELECT * 
FROM  `address` 
COLLATE utf8_general_ci
LIMIT 0 , 30

Does it have any (bad) side effect? I red about problems with indexing, performance? Or it is totally safe?

Answer 1

If practical, change the column definition(s).

ALTER TABLE tbl
    MODIFY col VARCHAR(...) COLLATE utf8_general_ci ...;

(You should include anything else that was already in the column definition.) If you have multiple columns to modify, do them all in the same ALTER (for speed).

If, for some reason, you cannot do the ALTER, then, yes, you can tweak the SELECT to change the collation:

The SELECT you mentioned had no WHERE clause for filtering, so let me change the test case:

Let's say you have this, which will find only 'San Jose':

SELECT *
    FROM tbl
    WHERE city = 'San Jose'

To include San José:

SELECT *
    FROM tbl
    WHERE city COLLATE utf8_general_ci = 'San Jose'

If you might have "combining accents", consider using utf8_unicode_ci. More on Combining Diacriticals and More on your topic.

As for side effects? None except for on potentially big one: The index on the column cannot be used. In my second SELECT (above), INDEX(city) is useless. The ALTER avoids this performance penalty on the SELECT, but the one-time ALTER, itself, is costly.