0

This is the Query:

SELECT
    A.*
FROM
    messages_thread_user mtu,
    LATERAL (SELECT
                 m.*, u.userName
             FROM
                 messages m
                     JOIN
                 user u using(userId)
             WHERE
                     m.threadId = mtu.threadId
             ORDER BY m.id DESC
             LIMIT 1) AS A
WHERE
    mtu.userId = 1;

This is Output:

+----+--------+----------+--------+---------------------+----------+----------+----------+
| id | userId | threadId | status | time                | message  | data_url | userName |
+----+--------+----------+--------+---------------------+----------+----------+----------+
|  6 |      1 |        1 |      0 | 2019-07-07 21:52:55 | hey      | NULL     | Rhidoy   |
|  8 |      5 |        4 |      0 | 2019-07-10 10:33:53 | hello    | NULL     | Rhidoy1  |
+----+--------+----------+--------+---------------------+----------+----------+----------+
2 rows in set (0.00 sec)

Avobe Query work well but do filesort in subqueries, i think filesorting is not so efficient for any Query. After searching here i found that if subqueries use index range than it may use filesort, there is no room for improve those query. But my subquries don't using index range it's using key lookup.

I want to know is there any room to improve this Query for not using filesort?

Below is Query Explain:

+----+-------------------+------------+------------+--------+----------------------------------------------------+----------------------------+---------+---------------------+------+----------+-----------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys                                      | key                        | key_len | ref                 | rows | filtered | Extra                                   |
+----+-------------------+------------+------------+--------+----------------------------------------------------+----------------------------+---------+---------------------+------+----------+-----------------------------------------+
|  1 | PRIMARY           | mtu        | NULL       | ref    | messages_thread_user_pk                            | messages_thread_user_pk    | 3       | const               |    2 |   100.00 | Using index; Rematerialize (<derived2>) |
|  1 | PRIMARY           | <derived2> | NULL       | ALL    | NULL                                               | NULL                       | NULL    | NULL                |    2 |   100.00 | NULL                                    |
|  2 | DEPENDENT DERIVED | m          | NULL       | ref    | messages_user_userId_fk,messages_threadId_id_index | messages_threadId_id_index | 3       | to_let.mtu.threadId |    3 |   100.00 | Using filesort                          |
|  2 | DEPENDENT DERIVED | u          | NULL       | eq_ref | PRIMARY                                            | PRIMARY                    | 3       | to_let.m.userId     |    1 |   100.00 | NULL                                    |
+----+-------------------+------------+------------+--------+----------------------------------------------------+----------------------------+---------+---------------------+------+----------+-----------------------------------------+
4 rows in set, 2 warnings (0.00 sec)

Below is Visual Explain:

Query Visual Explain

This is messages Table Structure:

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `threadId` mediumint(8) unsigned NOT NULL,
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '0->sent, 1->deliveried, 2->seen',
  `time` datetime DEFAULT CURRENT_TIMESTAMP,
  `message` text NOT NULL,
  `data_url` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `messages_user_userId_fk` (`userId`),
  KEY `messages_threadId_id_index` (`threadId`,`id`),
  CONSTRAINT `messages_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
  CONSTRAINT `messages_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

This is messages_thread_user Table Structure:

CREATE TABLE `messages_thread_user` (
  `threadId` mediumint(8) unsigned NOT NULL,
  `userId` mediumint(8) unsigned DEFAULT NULL,
  KEY `message_thread_user_messages_thread_id_fk` (`threadId`),
  KEY `message_thread_user_user_userId_fk` (`userId`),
  CONSTRAINT `message_thread_user_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
  CONSTRAINT `message_thread_user_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • Please tell us the task which you want to solve by your query. – Akina Jul 11 at 7:11
  • @Akina i want o get all the messages as conversation (threadId) for specific userId, in that i want to get the last message for this conversation and also the user info who sent the message. (it's something like facebook messenger) – Rhidoy Jul 11 at 7:21
  • If so get MAX(id) for each thread for the user you need in CTE, than join another tables to it in main query. – Akina Jul 11 at 7:27
  • i.e. WITH cte AS ( SELECT m.threadId, MAX(m.id) id FROM messages m JOIN messages_thread_user mtu USING (threadId) WHERE mtu.userId = 1 ) – Akina Jul 11 at 7:31
  • @Akina if i do so, as your example i need to group by threadId, also this do't keep order of the messasge desc, it keep order as threadId, so if i apply order than still this query using filesort. I need order because every conversation will be order by last message. so higher id will be on top posiiton. – Rhidoy Jul 11 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.