MySQL Forums
Forum List  »  Full-Text Search

Re: full text search in 2 tables
Posted by: Rick James
Date: July 26, 2010 11:21PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4176
July 21, 2010 11:30AM
Re: full text search in 2 tables
2062
July 26, 2010 11:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.