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 am using mariadb to store a large number of measurements. A common query I do selects for a data range. It takes a little long, something like 10 to 20 seconds, and it gets worse and worse, so I was playing around with my indices. I use EXPLAIN to see if the indices work.

I’ve got a website with a bunch of products, each of which is scored using a version of an algorithm and when listed, is joined on the following table: