What you have:
SELECT t1.*, t2.*
FROM articles AS t1
LEFT JOIN pages AS t2 ON t2.article_id = t1.id
WHERE MATCH ( t1.title, t1.content ) AGAINST ('Hello world!')
OR MATCH ( t2.title, t2.content ) AGAINST ('Hello world!')
AND t1.online='1'
ORDER BY t1.id, t2.page_index
will probably work. But this may run a lot faster:
( SELECT t1.*, t2.*
FROM articles AS t1
LEFT JOIN pages AS t2 ON t2.article_id = t1.id
WHERE MATCH ( t1.title, t1.content ) AGAINST ('Hello world!')
AND t1.online='1' )
UNION DISTINCT
( SELECT t1.*, t2.*
FROM articles AS t1
LEFT JOIN pages AS t2 ON t2.article_id = t1.id
WHERE MATCH ( t2.title, t2.content ) AGAINST ('Hello world!')
AND t1.online='1' )
ORDER BY t1.id, t2.page_index
This is because it won't use both indexes in a single SELECT. Using the two SELECTs in a UNION, you should be able to get the efficiency of both MATCHes using indexes.
You may want IN BOOLEAN MODE.
As for sorting by relevance -- The MATCH function gives you a relevance, but the two different tables may not deliver comparable relevances.