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.

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.