We had a serious performance problem with the openfire server and the mysql-slow.log pointed to the ofGroupUser table. An show index on that table showed me that all colums are primary key but the index which should be created on each of them is only available on the last column.
mysql> show index from ofGroupUser;
±------------±-----------±-------------------------±-------------±--------- -----±----------±------------±---------±-------±-----±-----------±------- -+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------------±-----------±-------------------------±-------------±--------- -----±----------±------------±---------±-------±-----±-----------±------- -+
| ofGroupUser | 0 | PRIMARY | 1 | groupName | A | NULL | NULL | NULL | | BTREE | |
| ofGroupUser | 0 | PRIMARY | 2 | username | A | NULL | NULL | NULL | | BTREE | |
| ofGroupUser | 0 | PRIMARY | 3 | administrator | A | 271137 | NULL | NULL | | BTREE | |
mysql> show index from ofGroupUser;
±------------±-----------±-------------------------±-------------±--------- -----±----------±------------±---------±-------±-----±-----------±------- -+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------------±-----------±-------------------------±-------------±--------- -----±----------±------------±---------±-------±-----±-----------±------- -+
| ofGroupUser | 0 | PRIMARY | 1 | groupName | A | NULL | NULL | NULL | | BTREE | |
| ofGroupUser | 0 | PRIMARY | 2 | username | A | NULL | NULL | NULL | | BTREE | |
| ofGroupUser | 0 | PRIMARY | 3 | administrator | A | 271137 | NULL | NULL | | BTREE | |
You can see this in the column cardinality. I added two indexes manually on groupname and username and the performance problem was solved. Is this a mysql problem or is the database table creation command wrong?
we use openfire 3.6.4
Guido