how to do reverse fulltext search in MySQL?

Question

By default it's like this:

select * from main_table where match(col1,col2) against('search_item');

but what I want to fetch is the reverse,

say,I've restored all the search_item(1000 records,for example),

and I want to see which of them matches a specified row in main_table.

Is that doable?

Answer 1

You could do something like:

SELECT * FROM search_items WHERE (SELECT col1 FROM main_table WHERE ID = XXX) LIKE CONCAT('%',search_item,'%');

That is going to be pretty darn slow if you have a huge dataset to get through.

If speed is an issue, another way to handle this (although admittedly a lot more complicated) is to get all of the data out of the database and build yourself a ternary search tree (also called a trie). Once you get through the overhead of building the trie, matching against input strings is lightning fast compared to brute force methods.

Answer 2

Well I realise this is ten months late but just for the sake of posterity. I think what you're saying is that you want all rows in the search_items table which would not match the query :

select * from main_table where match(col1,col2) against('search_item');

I'm going to assume that your main_table has some unique identifier column which I'll call unqid.

select * from main_table
WHERE
unqid not in (select unqid 
              from main_table  
              where match(col1,col2) against('search_item')
             )

My first thought was to use a MINUS operator but it turns out MySQL doesn't have one !