Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.