powered by Jive Software

Table indexes are not created on table ofGroupUser

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

Hi,

I did create OF-388 some days ago.

LG