I am using OF/IM and noticed that the following indexes are redundant (in MySQL): ofMucRoom_serviceid_idx index - masked by the primary composite index on ofMucRoom and ofRosterGroup_rosterid_idx which is also masked by the its table’s primary composite key. Both can be safely dropped.
We have also noted in our application the need for an index, ofGroupUser_username_idx, on username of OfGroupUser (which could equally be serviced by promoting username to the front of the composite primary key of that table thereby enabling a partial index) - this has resulted in a significant performance boost for our app (which has c. 250K entries in that table)
I am using MySQL 5.0.51a with (builtin) InnoDB as our default storage engine - the issue is, however, a SQL one so I imagine all version of MySQL are affected. It is likely that Oracle would exhibit similar effects.
The OF-388 correctly states that username already appears in an index but this index is the enforcement mechanism of a composite primary key and is therefore of limited (but nonetheless some) use for queries of the form “SELECT groupName FROM ofGroupUser WHERE username=?” as there is no partial index match (since username does not appear at the front of the index definition)
An EXPLAIN plan for such queries shows that an index scan is being performed (i.e., the data is being searched in index order based on the PRIMARY key index probably because it is a covering index) rather than an index access or lookup which would be preferable. This is mildly better than a full table scan.
As an aside our dataset is c. 250K and the index we created on username has a c. 17% selectivity - the EXPLAIN plan now changes to index lookup (i.e., type “ref”) without an index scan. This is a considerable improvement.
…here is some additional input in case it helps: I created a composite key similar to the PRIMARY but with username promoted to the front (ahead of groupName.) The EXPLAIN plan indicates that index lookup is now performed though there is some evidence of (potential) index scanning (mostly likely owing to the covering nature of the index.) The number of rows parsed is, however, comparable with an index on username alone i.e., probably the same performance. Interestingly MySQL prefers this composite index over all the alternatives.
Accordingly you have three alternatives: PRIMARY (i.e., do nothing), changing the definition of primary to put username to the front (at the cost of queries against groupName in the WHERE clause) and create a dedicated username index.
MySQL preference (for queries with USERNAME in the clause) is: Composite, username, PRIMARY.
If, however, you want a strategy that does not disadvantage groupName searches and works consistently for all databases then perhaps a dedicated username query is your best option even though maintaining two indexes affects storage and write performance (slightly)