DB indexes for muc_history table

Igor Khomenko
Added over 3 years ago

Do you have any guides or tutorials what are the best indexes for table "muc_history" ?

I see that this table is created from Tigase code without any indexes, but it can contain a lot of data, so would be great to setup some indexes to speed up the join room request

Replies (2)

Added by Andrzej Wójcik IoT 1 CloudTigaseTeam over 3 years ago

Most of queries executed on muc_history table are queries which use only room_name and timestamp fields (other fields are just retrieved) so indexes for this table would be:

  • index containing only room_name

  • index containing room_name and timestamp

Added by Igor Khomenko over 3 years ago


We decided to use next index:

CREATE INDEX muc_room_name_timestamp ON muc_history (room_name, timestamp);

Is it possible to add it to the DB schemas?