fuzzy search levenshtein vs full text indext

Question

What would perform better on a big table ~30million records grouped into 100_000 indexed buckets each having between 1 to 10_000 records?

1. MySQL's full text index on the entire dataset (presumably there is no way to narrow it down as cannot combine traditional indices with full text indices)

OR

2. postgresql's levenshtein distance calculated on pre-indexed data?

Would calculating the distance for 10_000 records consume a lot IO or is it an insignificant amount of data? Everyone warns about using this on big data sets but how big is too big in this context?

i.e. pseudo code queries with:

where indexed_bucket_id = "id12345678"
AND text_field fuzzy matches 'input string'

Answer 1

(MySQL:)

I don't think the Optimizer will do "Index merge intersect", which is just what you need. But check this out:

EXPLAIN
SELECT * FROM tbl
    WHERE indexed_bucket_id = "id12345678"
      AND MATCH(text_field) AGAINST ("...");

If you see that phrase in the 'Extra' column, it is good.

If not, then simulate it something like this (assuming PRIMARY KEY(id)):

SELECT tbl.*
    FROM ( SELECT id FROM tbl WHERE indexed_bucket_id = "id12345678" ) ) AS x
    JOIN ( SELECT id FROM tbl WHERE MATCH(text_field) AGAINST ("...") ) AS y
             USING(id)
    JOIN tbl USING(id)

Let me know if this works; I just invented it.