Slow Performance on 4.1.10 using UTF-8 and INNO tables

[Florian Gnägi]

Hi there

When loading our database in a 4.1.10 installation using latin1 as character set the following query takes about 0.7 seconds (165197 records, 7 distinct entries found)

select distinct resourcetypename from o_property;

Now since we change our whole app to UTF-8 we set the default encoding of mysql to utf8, converted the database dump using the iconv on the command line and loaded the dump in the new utf-8 database.

Now the exact same query takes about 6 seconds! Wow, this is factor 10, just by using UTF-8! I also tried is using the index on the resourcetypename explicitly - no effect.

Can anyone help me with this issue or explain me why on earth the whole database suddenly behaves like driving with the hand brake... ?

Thanks for any hints, tips and tricks!

- Florian


Here are some infos about the table:

mysql> explain o_property;
+------------------+----------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------------------+-------+
| id | bigint(20) | | PRI | 0 | |
| lastmodified | datetime | | | 0000-00-00 00:00:00 | |
| creationdate | datetime | YES | | NULL | |
| identity | bigint(20) | YES | MUL | NULL | |
| grp | varchar(255) | YES | MUL | NULL | |
| resourcetypename | varchar(255) | YES | MUL | NULL | |
| resourcetypeid | bigint(20) | YES | MUL | NULL | |
| category | varchar(33) | YES | MUL | NULL | |
| name | varchar(255) | | MUL | | |
| floatvalue | decimal(78,30) | YES | | NULL | |
| longvalue | bigint(20) | YES | | NULL | |
| stringvalue | varchar(255) | YES | | NULL | |
| textvalue | text | YES | | NULL | |
+------------------+----------------+------+-----+---------------------+-------+

mysql> show index from o_property;
+------------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| o_property | 0 | PRIMARY | 1 | id | A | 167544 | NULL | NULL | | BTREE | |
| o_property | 1 | resid_idx | 1 | resourcetypeid | A | 1269 | NULL | NULL | YES | BTREE | |
| o_property | 1 | category_idx | 1 | category | A | 460 | NULL | NULL | YES | BTREE | |
| o_property | 1 | name_idx | 1 | name | A | 193 | NULL | NULL | | BTREE | |
| o_property | 1 | restype_idx | 1 | resourcetypename | A | 6 | NULL | NULL | YES | BTREE | |
| o_property | 1 | grp_idx | 1 | grp | A | 5 | NULL | NULL | YES | BTREE | |
| o_property | 1 | FKB60B1BA5F7E870BE | 1 | identity | A | 11967 | NULL | NULL | YES | BTREE | |
+------------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+