In the query below,
tid are indexes of the
SELECT * FROM `replies` WHERE `from`="<userId>" OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>")
By using "OR", it seems that it does a full table scan (~3 million rows).
EXPLAIN says that a possible key would be
from, but then it doesn't use any.
However, in the query below,
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`="<userId>" OR `frid_gt`="<userId>"
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?