Article:
  MySQL FULLTEXT Searching
Subject:   Fulltext seach and joins
Date:   2004-10-12 13:32:23
From:   johnv8
In one of the queries above there is a join in the same clause as the fulltext search. This works in theory but as soon as you start using real databases you will run into significantly long queries when you do something like
SELECT *
FROM fulltext_table AS FT
INNER JOIN other_table AS OT ON OT.id = FT.id
WHERE
MATCH(FT.text) AGAINST ('search_string') LIMIT 1000


Even small databases of 2-4 million rows often take 2-3 minutes in computing this.


Disclaimer before you bash this:
yes, the rows are indexed and optimized. This occured on a fast server (dual processor 2Ghz, 2Gb ram). There are ways to make it fast but the example above will be painfully slow if you have a large DB.

Full Threads Newest First

Showing messages 1 through 1 of 1.

  • Fulltext seach and joins
    2004-10-12 13:34:35  johnv8 [View]

    Slight error with that query, I meant to say:

    SELECT *
    FROM fulltext_table AS FT
    INNER JOIN other_table AS OT ON OT.id = FT.id
    WHERE
    OT.id = 123 AND
    MATCH(FT.text) AGAINST ('search_string') LIMIT 1000