Slow Performance on 4.1.10 using UTF-8 and INNO tables
Posted by: Florian Gnägi
Date: February 25, 2005 10:41AM

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 | |
+------------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Performance on 4.1.10 using UTF-8 and INNO tables
6631
February 25, 2005 10:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.