MySQL "SET NAMES" near the top of the slow query log

Question

On a recently launched site, I noticed that, well over the actual heavy queries on the site, the most costly request, out of several million queries, is actually SET NAMES, which clocks over 2.3 seconds on average, while various multi-join union queries are well below 2 seconds. In the end, this places it near the top of the slow query log, and I just can't think of a way to optimize it.

This does not seem to be a common problem, as I found no reference to it googling.

Since SET NAMES is about collation, here is the related information:

• The MySQL Charset is UTF-8 Unicode (utf8)
• The database connection collation is utf8_unicode_ci
• the database and all its tables use utf8_general_ci

FWIW, this is running MySQL 5.0.45-log. The DB server is localhost.

Answer 1

If all queries are slow, SET NAMES can have a very low priority on the server, and therefore wait until the load dies down. If you have a lot of other queries that take a long time executing, you might want to try to optimize those first.

Another solution to this "problem", could be adding this to your my.cnf on the server:

[mysqld]
init-connect = 'SET NAMES utf8'

This makes sure the character set is set to UTF-8 when a client connects, so the client does not have to wait for the result of this "query". You probably want to disable the SET NAMES query in your software.

Mainly, I wouldn't worry too much unless you really have A LOT of SET NAMES that takes this long.