P2P message system design
deleted and seen get in the way of optimizations
GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id) seems strange.  Wouldn't this suffice:  GROUP BY m.to_id, m.from_id?
Since the user is either from_id or to_id, half the info is already known?
What is m.id &lt; 999999999 for?
IN ( SELECT ... ) often optimizes poorly.
You say "pagination", yet I don't see the inefficient OFFSET not the better "remember where I left off".
Use realistic limits, not blindly VARCHAR(255).
Each table has 3 timestamps.  You will never use some of them.
Don't use SELECT *; instead, spell out the columns that you really need.
You will need more indexes, but let's wait to see what more of the SELECTs look like.
LEFT JOIN account assumes that the desired account might be missing.  If so, there a data integrity problem.  So switch to JOIN; this may aid in optimizations.
LIMIT 100 -- Isn't that too much to put on a UI page?
AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1) is hard to optimize.  Use UNION of two SELECTs instead of OR, and add (tentatively) INDEX(from_id, to_id).  The reason is that the Optimizer has almost no techniques for handling OR; it simply ignores indexes and falls back on a table scan.


This might work:

AND (message.to_id, message.from_id) IN ((1, 2), (2, 1))


Here's the Changelog entry that hints at such:

----- 2013-12-03 5.7.3 Milestone 13 -- Functionality Added or Changed -- -----

The optimizer now is able to apply the range scan access method to queries of this form:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));


Previously, for range scans to be used it was necessary for the query to be written as:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
                      OR ( col_1 = 'c' AND col_2 = 'd' );


For the optimizer to use a range scan, queries must satisfy these conditions:

Only IN predicates can be used, not NOT IN.

There may only be column references in the row constructor on the IN predicate's left hand side.

There must be more than one row constructor on the IN predicate's right hand side.

Row constructors on the IN predicate's right hand side must contain only runtime constants, which are either literals or local column references that are bound to constants during execution.

EXPLAIN output for applicable queries will change from full table or index scan to range scan. Changes are also visible by checking the values of the Handler_read_first, Handler_read_key, and Handler_read_next status variables.