Boolean mode search on column of individual words MySQL

Question

I would like to perform a boolean mode search of my table without a full-text index. My dataset requires extremely fine levels of detail, so my documents have each word being it's own row, rather than each document being a row. Is it possible to make a regular index the subject of a boolean mode search, rather than having the full-text index (obviously useless here) as the subject?

Answer 1

It sounds like your data is in a document-word structure.

One way to make this work is by using standard comparisons and a having clause. The following would search for "wordA" and "wordB" but not "WordC":

select DocumentId
from DocumentWords dw
group by DocumentId
having max(word = 'WordA') > 0 and
       max(word = 'WordB') > 0 and
       mac(word = 'WordC') = 0;

If you want to use a full text index, then concatenate your documents together, doing something like:

select DocumentId, group_concat(word separator ', ')
from DocumentWords dw
group by DocumentId

Store this as a separate table and use the full text index there.