Using "OR" against two indexed columns doesn't use indexes In the query below, from and tid are indexes of the replies table.

SELECT * FROM `replies`
WHERE `from`="&lt;userId&gt;"
OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="&lt;userId&gt;")


By using "OR", it seems that it does a full table scan (~3 million rows).
The EXPLAIN says that a possible key would be from, but then it doesn't use any.

However, in the query below, frid_lt and frid_gt are indexed. The two columns are in a complex index (frid_lt, frid_gt), but frid_gt has also its own index.

SELECT `mid` FROM `messages`
WHERE `frid_lt`="&lt;userId&gt;" OR `frid_gt`="&lt;userId&gt;"


And this query DOES use two indexes. The EXPLAIN says "index_merge" and "Using sort_union(frid_lt,frid_gt); Using where".

Why does the first query not use an index merge?
Is there any improvement I can make to make the engine use an index merge as well?
OR does not optimize well.  A common workaround is to use UNION:

( SELECT * FROM replies WHERE `from` = "..." )
UNION ALL   -- or UNION DISTINCT if you know there are no dups
( SELECT r.* FROM replies AS r
    JOIN posts AS p  ON p.tid = r.tid
    WHERE p.from = "..." )


Notice that I also avoided the usually-inefficient IN ( SELECT ... )

For further performance, have these indexes:

replies:  INDEX(`from`)
posts:    INDEX(`from`, tid)  -- in this order
replies:  INDEX(tid)


(And note that the PRIMARY KEY is an index, so don't add a redundant index.)

In your second example, the "index merge" that you experienced may or may not be faster than a UNION.

Oh, it's an UPDATE

To optimize UPDATE, do two separate UPDATEs (no UNION, no OR).  One straightforwardly checks from.  The other is a "multi-table UPDATE" (see the manual) similar to the second select above.