I’m working on a chatting app and running MariaDb. I’m trying to create a query to get all the recent conversations, i.e. last message and from which user, similar to iMessage/Messenger/WhatsApp does when you open it. The chatmessages table contains 4+M of rows. I tried using PARTITION BY OVER to get the desired results, but was getting very bad performance (1.5 seconds). I tried another approach to grouping but after spending a couple of days optimizing it, i ended up with a worst time 🤡 (1.7s).

I’m working on a chatting app and running MariaDb. I’m trying to create a query to get all the recent conversations, i.e. last message and from which user, similar to iMessage/Messenger/WhatsApp does when you open it. The chatmessages table contains 4+M of rows. I tried using PARTITION BY OVER to get the desired results, but was getting very bad performance (1.5 seconds). I tried another approach to grouping but after spending a couple of days optimizing it, i ended up with a worst time 🤡 (1.7s).