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).

What am I doing wrong, how can I optimize this. I’ll share the 2 variations I created. Ideally im trying to get the query to execute in under 0.2sec . Main tables are the chatmessages holding the messages, sessions where each chatmessage refer to a session id, and visitors, representing the visitors. Each session is linked with a single visitor.

First Try – Using PARTITON OVER

There is a lot going on here, but the partition by alone take 1s to execute.

DROP PROCEDURE IF EXISTS `GetRecentConversations`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecentConversations`(
    `limit` INT,
    `offset` INT,
    `agent` CHAR(95),
    `property` VARCHAR(100),
    `closed` TINYINT
    )
BEGIN
    SET `limit` = IFNULL(`limit`, 30);
    SET `offset` = IFNULL(`offset`, 0);
    SET `agent` = IFNULL(`agent`, '');
    SET `property` = IFNULL(`property`, 0);
    SET `closed` = IFNULL(`closed`, 0);
    SELECT * 
    FROM
    (
        SELECT A.VisitorId, V.Name AS 'VisitorName', V.Picture AS 'ProfilePic', GROUP_CONCAT(DISTINCT CT.`TagsId`) AS 'TagsId' , 
            V.IsBlocked, A.MessageId, A.Message, A.CreatedAt AS 'MessageCreatedAt' , A.Attachment, A.ArticleId, A.SessionId, 
            V.PropertyId, P.Name AS 'PropertyName', AG.Agents, IF(AG.Agents IS NULL, 0,1) AS 'IsAttended',
            MAX( CASE WHEN A.FromAgentId IS NULL THEN A.CreatedAt END) AS 'LastSeen', A.rn
        FROM (
          SELECT S.Id AS 'SessionId', S.`VisitorId`, C.Message, C.Attachment, C.ArticleId, C.Id AS 'MessageId', C.CreatedAt, S.EndedOn, C.FromAgentId,
                ROW_NUMBER() OVER (PARTITION BY S.`VisitorId` ORDER BY  C.`CreatedAt` DESC) AS rn
          FROM Sessions AS S
          LEFT JOIN ChatMessages C ON S.Id = C.SessionId
          WHERE S.IsDeleted = 0
        ) AS A
        LEFT JOIN Visitors V ON A.VisitorId = V.Id
        LEFT JOIN Properties P ON V.PropertyId = P.Id
        LEFT JOIN `contacttagvisitor` CT ON V.Id = CT.VisitorsId
        LEFT JOIN (
                SELECT SessionId, GROUP_CONCAT(AgentId) AS 'Agents'
                FROM sessionagents SA 
                LEFT JOIN aspnetusers U ON SA.AgentId = U.Id
                WHERE U.IsBot = 0
                GROUP BY SessionId
            ) AS AG ON A.SessionId =  AG.SessionId
        WHERE     ( (`agent`    = '') OR ( FIND_IN_SET( `agent`, AG.Agents ) > 0  )  ) AND 
              ( (`property` = 0 ) OR ( FIND_IN_SET( CAST(V.PropertyId AS CHAR),`property`) > 0)  ) AND 
              ( ( (`closed` = 0) AND (A.EndedOn IS NULL) ) OR 
                ( (`closed` = 1) AND (A.EndedOn IS NOT NULL) )
                )
        GROUP BY A.VisitorId
        HAVING (rn = 1) /* get only the last message in the session, done after grouping (HAVING) because we need the MAX LastSeen to work correctly */
        
    ) AS F
    ORDER BY MessageCreatedAt DESC LIMIT `limit` OFFSET `offset`;
END$$

DELIMITER ;

Second Try – Tried to avoid window functions

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecentConversationsOptimized`(
    `limit` INT,
    `offset` INT,
    `agent` CHAR(95),
    `property` VARCHAR(100),
    `closed` TINYINT
    )
BEGIN

    SET `limit` = IFNULL(`limit`, 30);
    SET `offset` = IFNULL(`offset`, 0);
    SET `agent` = IFNULL(`agent`, '');
    SET `property` = IFNULL(`property`, 0);
    SET `closed` = IFNULL(`closed`, 0);

    WITH agents AS (
        SELECT SessionId AS 'AgentsSessionId', GROUP_CONCAT(AgentId) AS 'Agents'
        FROM sessionagents SA 
        LEFT JOIN aspnetusers U ON SA.AgentId = U.Id
        WHERE U.IsBot = 0
        GROUP BY SessionId
    ),
    wtags AS (
        SELECT vs.Id AS 'VId', GROUP_CONCAT(DISTINCT CT.`TagsId`) AS 'TagsId' 
        FROM visitors vs
        INNER JOIN `contacttagvisitor` CT ON CT.VisitorsId = vs.Id
        GROUP BY vs.Id  
    ),
    ms AS (
        SELECT s.VisitorId, s.`Id` AS 'SessionId',  m.Message, m.`Attachment`, m.`ArticleId`, m.`Id` AS 'MessageId', m.`CreatedAt` AS 'MessageCreatedAt', s.`EndedOn`, m.`FromAgentId`,
            V.Name, V.Picture, V.IsBlocked, 
            P.Name AS 'PropertyName', P.Id AS 'PropertyId'
        FROM chatmessages m
        LEFT JOIN sessions S ON s.Id = m.SessionId
        LEFT JOIN visitors V ON S.VisitorId = V.Id
        LEFT JOIN properties P ON V.`PropertyId` = P.Id

        /*LEFT JOIN (
                SELECT SessionId, GROUP_CONCAT(AgentId) AS 'Agents'
                FROM sessionagents SA 
                LEFT JOIN aspnetusers U ON SA.AgentId = U.Id
                WHERE U.IsBot = 0
                GROUP BY SessionId
            ) AS AG ON S.Id =  AG.SessionId*/
        WHERE     /*( (@agent    = '') OR ( FIND_IN_SET( @agent, AG.Agents ) > 0  )  ) AND */
              ( (`property` = 0 ) OR ( FIND_IN_SET( CAST(V.PropertyId AS CHAR),`property`) > 0)  ) AND 
              ( ( (`closed` = 0) AND (S.EndedOn IS NULL) ) OR 
                ( (`closed` = 1) AND (S.EndedOn IS NOT NULL) )
                )   
        
    ) 
    
    SELECT ms.VisitorId, ms.Name AS 'VisitorName' , ms.Picture AS 'ProfilePic', wt.TagsId, 
        ms.IsBlocked, ms.MessageId, ms.Message, ms.MessageCreatedAt, ms.Attachment, ms.ArticleId, ms.SessionId, 
        ms.PropertyId, ms.PropertyName,  ag.Agents, IF(ag.Agents IS NULL, 0,1) AS 'IsAttended', LS.LastSeen
    FROM ms 
    LEFT JOIN wtags wt ON ms.VisitorId = wt.VId
    LEFT JOIN agents ag ON ms.SessionId = ag.AgentsSessionId
    LEFT JOIN ( 
        SELECT VisitorId AS 'VLSId', MAX( CASE WHEN ms.FromAgentId IS NULL THEN ms.MessageCreatedAt END) AS 'LastSeen'
        FROM ms
        GROUP BY VisitorId
    ) LS ON ms.VisitorId = LS.VLSId
    WHERE (VisitorId,MessageCreatedAt) IN 
        ( SELECT VisitorId, MAX(MessageCreatedAt)
          FROM ms
          GROUP BY VisitorId
        )
    ORDER BY MessageCreatedAt DESC LIMIT `limit` OFFSET `offset`;

    
END$$

DELIMITER ;