Currently, the search query that I’m using for searching my forum looks like this (this particular search was for the term “6.18”) (this is just part of the where clause that deals with searching:

(
    INSTR(LOWER(author), "6.18")
 OR INSTR(LOWER(subject), "6.18")
 OR id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(text) AGAINST("6.18")
              AND moderated IS NOT NULL)
 OR INSTR(LOWER(email), "6.18")
OR INSTR(LOWER(DATE(updated)), "6.18")
)

To optimise it I realise that, depending on the search query, I could cut out a few of those where clauses (eg there’s no point searching the updated field – a date field – if there’s anything but numbers and hyphens in the query term (I convert words like July to -07- beforehand)

But how about spreading the fulltext index across multiple fields and just having a single match/against clause? Would that be a better way of doing things than all those WHERE clauses?

eg create the index with something like this:

ALTER TABLE messages ADD FULLTEXT INDEX `messages-meta-information` (author, subject, email, updated);

So the search query might look like this:

(
    id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(author, subject, email, updated) AGAINST("6.18")
 OR id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(text) AGAINST("6.18")
              AND moderated IS NOT NULL)
)