prosody 0.11 is very slow with SQL(ite)-based MAM (on the order of tens of seconds on large archives), when querying for "the last 20 messages with any JID": Mar 12 07:21:30 yax.im:mam debug Archive query, id b3638685-e6b4-432a-8183-ec03d6cb5a3d with anyone from the dawn of time until now Mar 12 07:21:30 sql debug [select] SELECT COUNT(*) FROM "prosodyarchive" WHERE "host" = 'yax.im' AND "user" = '*censored*' AND "store" = 'archive2' Mar 12 07:21:31 sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'yax.im' AND "user" = '*censored*' AND "store" = 'archive2' ORDER BY "sort_id" DESC LIMIT 21; Mar 12 07:21:42 yax.im:mam debug Archive query b3638685-e6b4-432a-8183-ec03d6cb5a3d completed Mar 12 07:21:42 c2s5571b2bf27c0 debug Sending[c2s]: <iq type='result' to='*censored*@yax.im/dino.bb81c358' id='15635680-8ae5-4eca-bae5-fdb8dede7ef2'> Mar 12 07:21:42 mod_log_slow_events warn Slow event 'iq-set/self/urn:xmpp:mam:2:query' took 11.28s: ip="*censored*", full_jid="*censored*@yax.im/dino.bb81c358", type="c2s", host="yax.im", stanza="<iq type='set' from='*censored*@yax.im/dino.bb81c358' id='15635680-8ae5-4eca-bae5-fdb8dede7ef2'><query queryid='b3638685-e6b4-432a-8183-ec03d6cb5a3d' xmlns='urn:xmpp:mam:2'><x type='submit' xmlns='jabber:x:data'><field type='hidden' var='FORM_TYPE'><value>urn:xmpp:mam:2</value></field></x><set xmlns='http://jabber.org/protocol/rsm'><before/><max>20</max></set></query></iq>" This can be significantly improved (query time goes down to 10..40ms) with this index: CREATE INDEX "prosodyarchive_sort" ON "prosodyarchive" ("host", "user", "store", "sort_id");

• tags Status-Accepted Milestone-0.11

Fixed in https://hg.prosody.im/0.11/rev/3fce670e0733

• tags Status-Fixed