MySQL database query takes a certain amount of time

Question

I have a large message database with 24,000 rows:

Display rows 0-24 (2455455 rows in total, query takes 0.0006 seconds). 

messages, so I need to load conversations faster, for users with less conversations, the loading is as follows (user has 3.2k conversations):

Display rows 0-24 (3266 rows in total, query takes 0.0345 seconds) [id: 5009666... ​​- 4375619...]. 

Slower loading for users with a lot of conversations (user with 40k conversations):

Display rows 0-24 (40296 rows in total, query takes 5.1763 seconds) [id: 5021561... - 5015545...]. 

I use index keys for these columns:

id, to_id, from_id, time, seen

Database table:

CREATE TABLE `messages` (
  `id` int(255) NOT NULL,
  `to_id` int(20) NOT NULL,
  `from_id` int(20) NOT NULL,
  `message` longtext NOT NULL,
  `time` double NOT NULL,
  `seen` int(2) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALUES
(2, 6001, 2, 'Hi there', 1587581995.5222, 1);


ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `time_idx` (`time`),
  ADD KEY `from_idx` (`from_id`),
  ADD KEY `to_idx` (`to_id`),
  ADD KEY `seenx` (`seen`),
  ADD KEY `idx` (`id`);


ALTER TABLE `messages`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;

I use the following query:

SELECT
  *
FROM
  messages,
  (
    SELECT
      MAX(id) as lastid
    FROM
      messages
    WHERE
      (
        messages.to_id = '1' -- the ID to compare to (the ID of the logged in user)
        OR messages.from_id = '1' -- the ID to compare to (the ID of the logged in user)
      )
    GROUP BY
      CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) as conversations
WHERE
  id = conversations.lastid
ORDER BY
  messages.id DESC

I don't know how to make it faster for users with a lot of conversations, if I should recreate the database structure.

Answer 1

Hmm, maybe you could try adding an index to your table: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql -tables#:~:text=Creating Indexes&text=The statement to create index, the index must be distinct. Make sure to add a composite index based on the rows you query.

If this does not improve your query time, then the query should be improved.

Answer 2

Notice:

• Use UNION instead of OR (see below)
• There are redundant keys. PRIMARY KEY is a key, so delete KEY(id)
• Don't blindly create indexes for every column; instead, use queries to determine which indexes, especially composite indexes, are actually useful.
• In GROUP BY and ORDER BY, CONCAT is unnecessary and may be counterproductive.
• For INT type, the length field is ignored. What you have is a limit of 2 billion values. (Is this overkill for seen, assuming it only has 0 or 1?)
• Use new syntax: JOIN..ON.
• If seen is only true/false, then delete its index. (Or show me a query you think you'd benefit from.)

CONCAT-LEAST-GREATEST - Is this for constructing a "friends_id"? Maybe what you really want is a "conversation_id"? Currently, two users can never have multiple "conversations", right?

If necessary, create a new column for conversation_id. (Currently, GROUP BY is inefficient.) The following code eliminates the need for such an id.

( SELECT lastid FROM (
    ( SELECT from_id, MAX(id) AS lastid FROM messages
           WHERE to_id = ? GROUP BY from_id )
    UNION DISTINCT
    ( SELECT to_id,   MAX(id) AS lastid FROM messages 
           WHERE from_id = ? GROUP BY to_id )
                     ) AS x
) AS conversations

And have these "covering" and "composite" indexes:

INDEX(to_id, from_id, id)
INDEX(from_id, to_id, id)

Remove KEY(to_id), KEY(from_id) as my new index can handle all other tasks for these two indexes.

I think this has the same effect but runs faster.

Combine them:

SELECT  *
    FROM (
            ( SELECT from_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages
                  WHERE to_id = ? GROUP BY from_id )
            UNION ALL
            ( SELECT to_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages 
                  WHERE from_id = ? GROUP BY to_id )
         ) AS latest
    JOIN  messages  ON messages.id = latest.lastid
    ORDER BY  messages.id DESC

(Add these two indexes)

More

I once mistakenly thought that UNION DISTINCT could replace the need for conversation_id. but it is not the truth. I immediately saw some solutions:

• Add a conversation_id and use it for deduplication. (Also, I changed UNION DISTINCT to UNION ALL, making the query slightly faster without changing the results.)
• Put my query results into a temporary table containing (from_id, to_id, latestid); then use your CONCAT-LEAST-GREATEST technique to deduplicate the conversation; and finally JOIN it with the messages table, to get additional columns.
• This temporary table technology makes writing and debugging easier. My third suggestion is just to combine these parts into one (unreadable) query with nested Select statements 3 levels deep.