Good Evening All,
I'm trying to pre-filter a table such that the more complex MATCH ... AGAINST query doesn't need to process every row in the DB. I tried obtaining a list of identifiers (query 3) which I feed into query 2 to limit the list. This new list is finally used to find the string of interest.
The syntax works fine however MySQL complains that this the new table, generated by query 2, does not have a FULLTEXT index (The original table does and it works fine without subquery)
SELECT *** QUERY 1 - Final search on pre-filtered list lid, url, MATCH (`original`) AGAINST ('trevelling' IN NATURAL LANGUAGE MODE) FROM (SELECT *** QUERY 2 - table with filtered entries lid, url,original, FROM hcladb.links WHERE lid in ( SELECT *** QUERY 3 - list of filtered IDs lid FROM hcladb.filtervalues WHERE (filtype = 2 AND filval = 54) OR (filtype = 2 AND filval = 29) OR (filtype = 1 AND filval = 53) GROUP BY lid HAVING count(Distinct filtype) > 1 ) ) WHERE MATCH (`original`) AGAINST ('trevelling' IN NATURAL LANGUAGE MODE);
Can anyone tell me if its possible to maintain the FULLTEXT index or advise how you can efficiently pre-filter tables? Note: The table will eventually be a couple 100k rows, hence want to avoid a JOIN for performance reasons.
Thanks in advance folks, John