I’ve been looking at doing some database optimisation to try and reduce startup load on one of our Openfire servers (mainly adding some indexes for extra queries we do).
While looking at some of the tables that were being queried I noticed that the JID column in ofMucMember and ofMucAffiliation are still TEXT columns (on MySQL). DB version 19 changed the JID column of jiveRoster/ofRoster VARCHAR(1024).
These columns should changed for consistency and performance reasons.
Cheers
Mike
ofRoster (correct):
mysql> desc ofRoster;
±---------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±--------------±-----±----±--------±------+
| rosterID | bigint(20) | NO | PRI | NULL | |
| username | varchar(64) | NO | MUL | NULL | |
| jid | varchar(1024) | NO | MUL | NULL | |
| sub | tinyint(4) | NO | | NULL | |
| ask | tinyint(4) | NO | | NULL | |
| recv | tinyint(4) | NO | | NULL | |
| nick | varchar(255) | YES | | NULL | |
±---------±--------------±-----±----±--------±------+
7 rows in set (0.07 sec)
ofMucAffiliation (incorrect):
mysql> desc ofMucAffiliation;
±------------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-----------±-----±----±--------±------+
| roomID | bigint(20) | NO | PRI | NULL | |
| jid | text | NO | PRI | NULL | |
| affiliation | tinyint(4) | NO | | NULL | |
±------------±-----------±-----±----±--------±------+
3 rows in set (0.00 sec)
ofMucMember (incorrect):
mysql> desc ofMucMember;
±----------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------±------+
| roomID | bigint(20) | NO | PRI | NULL | |
| jid | text | NO | PRI | NULL | |
| nickname | varchar(255) | YES | | NULL | |
| firstName | varchar(100) | YES | | NULL | |
| lastName | varchar(100) | YES | | NULL | |
| url | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| faqentry | varchar(100) | YES | | NULL | |
±----------±-------------±-----±----±--------±------+
8 rows in set (0.01 sec)