Optimize big MySQL table for faster MAX query latency

Question

I have the following table in my MySQL DB:

CREATE TABLE messages (
  id         VARCHAR(36)   NOT NULL PRIMARY KEY,
  chat_id    VARCHAR(36)   NOT NULL,
  author_id  VARCHAR(36)   NOT NULL,
  content    VARCHAR(500)  character set utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  visible    TINYINT NOT NULL DEFAULT 1,
  request_id VARCHAR(128),
  created_at BIGINT signed NOT NULL,
  updated_at BIGINT signed NOT NULL,

  UNIQUE INDEX messages_chat_id_created_at(chat_id, created_at DESC)
);

It has a size of ~400GB and ~700 mil rows.

The only query I run on this table is that one:

            SELECT
               *
            FROM
               messages
            WHERE
               chat_id = :chatId
               AND created_at <= :createdAt
               AND visible = 1
            ORDER BY created_at DESC
            LIMIT 20

The table is continuously growing, and although in 90% of cases only the most recent data is fetched, I have to keep old messages in the DB both due to our retention policy and to support cases where users come back to their old conversations.

The problem I have is that although p99 oscillates around 60ms, I get a pretty consistent MAX latency of as much as 750ms.

Index range scan on m using messages_chat_id_created_at, with index condition: ((m.chat_id = ?) and (m.created_at))

Rows returned: 10

Latency: 370.9 ms

Is there a quick win I can apply to speed things up a little?

Your query says: created_at <= :createdAt, but you say: "in 90% of cases only the most recent data is fetched". This doesn't seem to match? I don't understand. — KIKO Software
– KIKO Software, Commented Aug 14 at 10:26
It's used for pagination. The first call is with createdAt = NOW and if user wants to load more messages then client can call the same method with createdAt set to the timestamp of the oldest message he has loaded so far. — MM.
– MM., Commented Aug 14 at 11:14
Have you looked into table partitioning based on created_at or the primary key? — KIKO Software
– KIKO Software, Commented Aug 14 at 15:31
Why are members *_id strings and not integers? For VARCHAR(36) and collation utf8mb4* up to 144 bytes must be compared, instead of 4 or 8 bytes for int or bigint. — Wiimm
– Wiimm, Commented Aug 15 at 7:29

Tim Biegeleisen · Accepted Answer · 2025-08-14 10:28:30Z

3

You could try including visible in the index so that it completely covers the entire WHERE clause of your query:

CREATE INDEX idx ON messages (chat_id, visible, created_at);

Because created_at is last in the index, it should be easy for the optimizer to satsify the LIMIT clause.

answered Aug 14 at 10:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MM. Aug 14 at 11:17

Thanks! I made a mistake when I was formatting the query. It should be visible = 1. And that's the value that almost all rows have (it's very uncommon for this condition to not be true). I just gave it a shot by getting rid of that extra condition entirely, loading 30 messages, and getting rid of those that have incorrect visible on the service side but MAX didn't move.

Rick James · Accepted Answer · 2025-08-15 19:53:20Z

UUIDs -- It looks like id (and other columns are UUIDs. The main problem with them (in your query) is that each of the 20 (or 30) rows will be 'randomly' scattered around the 400GB of data, leading to lots of I/O. Less important: the size is 1+36 (not the 144 mentioned by someone else). It could be shrunk by using the BINARY(16) (16 bytes). cf: [_UUIDs_](https://mysql.rjweb.org/doc.php/uuid) . The table might shrink from 400 GB to 360 GB; this is arguably 'not worth the effort'.
innodb_buffer_pool_size -- This should be about 70% of _available RAM. If that is more than what you have now, raising it may improve performance.
Pagination -- You hint at pagination, but fail to show whether you are using OFFSET. Here is my rant against OFFSET and how to "remember where left off" instead: [_Pagination_](https://mysql.rjweb.org/doc.php/pagination)
LIMIT 20 -- Simply decreasing the 20 would make the page more responsive (assuming it is the I/O problem mentioned above). However, you may decide that more pages is worse than the sluggishness of pages. Or, what about 10 on the first page, then 30 on subsequent pages? (This is a UI question, beyond the scope of this forum.)
Change PRIMARY KEY. This may be the 'best solution' because you have only one query to optimize. PRIMARY KEY(chat_id, created_at), UNIQUE(id). This would "cluster" all 20 rows together on disk in spite of the UUID downsides. Such an ALTER TABLE woud take a long time for 400GB. (Caveat: I have not pondered the impact of needing visible=1 in the query. What percentage of rows have 0 vs 1?)

Collectives™ on Stack Overflow

Optimize big MySQL table for faster MAX query latency

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related