3

Below is my MESSAGES_THREAD table:

CREATE TABLE `messages_thread` (
  `id` varchar(64) NOT NULL,
  `creator` mediumint(9) NOT NULL,
  `other` mediumint(9) NOT NULL,
  `time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `messages_thread_creator_index` (`creator`),
  KEY `messages_thread_other_index` (`other`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Below is my MESSAGES table:

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `threadId` varchar(64) 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_threadId_status_index` (`threadId`,`status`),
  KEY `index5` (`userId`),
  KEY `message_threadId_id_index` (`threadId`,`id` DESC),
  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=2522 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I want to get the last message id from MESSAGES table according to its threadId and to doing this i tried below two Queries both result are ok but those are not so efficient. Second Query seems quite efficient but not up-to the mark I accepted. How can I write this Query efficiently?


Below is First QUERY:

SELECT max(m.id), mt.other 
FROM messages_thread mt 
join messages m ON mt.id = m.threadId 
WHERE mt.creator=1 
group by mt.other;

Below it's Graphical Execution Plan:

enter image description here

Below it's Session Status:

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  1
Handler_read_key    41
Handler_read_last   0
Handler_read_next   2561
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

Below is Second Query (which seems little bit optimized):

SELECT m2.id, mt.other 
FROM messages_thread mt 
inner join ( select max(id) id, threadId 
           from messages m 
           group by threadId) as m2 on mt.id=m2.threadId
WHERE mt.creator=1;

Below it's Graphical Execution Plan:

enter image description here

Below it's Session Status:

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  1
Handler_read_key    122
Handler_read_last   1
Handler_read_next   0
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   41
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   40

Query Execution details with LATERAL for suggested by member Madhur Bhaiya

Below is the Query suggested by Madhur Bhaiya LATERAL:

SELECT m2.id, mt.other 
FROM messages_thread mt 
join lateral (select max(m.id) as id
               from messages m 
               where m.threadId = mt.id ) as m2
WHERE mt.creator=1;

Below it's Graphical Execution Plan:

enter image description here

Below it's Session Status:

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    41
Handler_read_last   0
Handler_read_next   2561
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   80
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   40

Below it's Explain Data:

1   PRIMARY mt      ref index5,messages_thread_creator_index,message_creator_other_index    message_creator_other_index 3   const   40  100.00  Using index; Rematerialize (<derived2>)
1   PRIMARY <derived2>      ALL                 2   100.00  
2   DEPENDENT DERIVED   m       ref messages_threadId_status_index,messages_threadId_index,messages_threadId_id_index   messages_threadId_status_index  258 tolet.mt.id 61  100.00  Using index

  • Test ALTER TABLE messages_thread ADD UNIQUE INDEX idx1 (creator, id, other). – Akina Oct 2 at 7:08
  • @Akina that not helping also. both results same. – Rhidoy Oct 2 at 18:19
  • Look EXPLAIN. What indexes are used? beautiful != visible and clear. – Akina Oct 2 at 18:25
  • @Akina it using indexes but not optimized for max(id) values. – Rhidoy Oct 2 at 18:37
  • not optimized for max(id) values Grouping by one table, max by another... I cannot find the point fo optimize. – Akina Oct 2 at 18:43
0

I noticed the plans 1-3 use Full Table Scan. It's probably not a problem with small tables (as far as I understand messages_thread has only 40 records). So if you expect the tables to grow and have variety of mt.creator values, then you would probably want to access mt by index and not scanning it.

I think the query optimizer would switch in this mode automatically once the tables grow. But if you want to see it with the current data, you may try to use hint:

SELECT max(m.id), mt.other 
FROM messages_thread mt USE INDEX(messages_thread_creator_index)
join messages m ON mt.id = m.threadId 
WHERE mt.creator=1 
group by mt.other;

Or create additional index:

create index mt_creator_thread_id_index on messages_thread(creator, id);

(But the latter is less likely to affect execution plan on small data.)

Additional:

Actually, on my system (5.7 with empty tables), Plan 2 doesn't use Full Table Scan either with or without USE INDEX (message_threadId_id_index), it accesses m2 by <auto_key0> index based on mt.id :

mysql> explain SELECT m2.id, mt.other  FROM messages_thread mt  inner join ( select max(id) id, threadId             from messages m             group by threadId) as m2 on mt.id=m2.threadId WHERE mt.creator=1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: mt
   partitions: NULL
         type: ref
possible_keys: PRIMARY,messages_thread_creator_index
          key: messages_thread_creator_index
      key_len: 3
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 258
          ref: test.mt.id
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: m
   partitions: NULL
         type: index
possible_keys: messages_threadId_status_index,message_threadId_id_index
          key: messages_threadId_status_index
      key_len: 260
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index


mysql> explain SELECT m2.id, mt.other  FROM messages_thread mt  inner join ( select max(id) id, threadId             from messages m USE INDEX (message_threadId_id_index)            group by threadId) as m2 on mt.id=m2.threadId WHERE mt.creator=1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: mt
   partitions: NULL
         type: ref
possible_keys: PRIMARY,messages_thread_creator_index
          key: messages_thread_creator_index
      key_len: 3
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 258
          ref: test.mt.id
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: m
   partitions: NULL
         type: index
possible_keys: messages_threadId_status_index,message_threadId_id_index
          key: message_threadId_id_index
      key_len: 266
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index

It still shows Rows: 2 for the derived table, but that's probably an estimate made by query optimizer and not that it actually reads two records for every id value.

  • actually the main problem is with messages table max(m.id) it's not optimizing, nor with sub-queries or nor with lateral. messages_thread table is ok here. – Rhidoy Oct 2 at 19:19
  • You mean you want max() to be received from index and not a calculated group? Like having (mt.other, m.id) index? I don’t see how it could be done in MySQL. Maybe materialized view feature of other database engines could help here. – ichalov Oct 2 at 19:45
  • On the other hand, plan 3 doesn’t have GROUP step, it really gets max() from index in some sense. Another thought is that you could move or duplicate creator and other fields from mt to m. Then JOIN wouldn’t be needed at all. – ichalov Oct 2 at 20:07
  • yeah, plan 2 or plan 3 is the discussion material, in plan 3 with materialized table it should use index and optimize max(), the problem is it using index but not optimizing it. that the problem why it do not optimizing max? what you think? and move field from mt to m is just waste of space because those filed related only with mt. – Rhidoy Oct 2 at 20:36
  • Ah, you probably mean plan 3 should use message_threadId_id_index and not messages_threadId_status_index. Can you try to just hint it to the sub-query? But I don't understand why it wasn't chosen in the first place. – ichalov Oct 2 at 20:54
1

MySQL 8.0.14 has introduced Lateral Derived Table. In your second query, you are computing max() value for all the threadId, whether you need it or not during the JOIN. You can avoid this materialization of m2 (a costly process, which is basically temp table creation, either in-memory, or disk (if too big)), by using the following instead:

SELECT max(m2.id), mt.other 
FROM messages_thread mt 
join lateral ( select max(m.id) as id
               from messages m 
               where m.threadId = mt.id ) as m2
WHERE mt.creator=1 
group by other;

Also, try defining a new composite index on messages_thread table for Group By Optimization in the main SELECT query: (creator, other). You can benchmark this new query with and without new index suggestion, to decide whether to keep it or not.

  • LATERAL() not helping here with index or without index on (creator, other). it's processing same row what process my first query. Also it's still using materialization table. – Rhidoy Oct 2 at 18:14
  • yeah, i updated with it's performance data please see again – Rhidoy Oct 2 at 18:31
  • i also tried with lateral perviously but got same result,well i'm adding a db_fiddle, please wait some time. – Rhidoy Oct 2 at 18:47
  • previously i never used db-fiddle, i'm not fully known how to work with it. but i tried to make sql dump of my tables and the messages table is to big, its almost 701kb, that's large text file is possible to upload in db-fiddle? also there has no way to upload sql file only text input. – Rhidoy Oct 2 at 19:13
  • @Rhidoy just copy create table statement and some 200 insert statements (rows) from the SQL dump – Madhur Bhaiya Oct 2 at 19:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.