Timeline for fuzzy search levenshtein vs full text indext
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 30, 2023 at 15:41 | comment | added | Rick James | Some of the list above must be tackled by checking every word in the dataset; some can be sped up by indexing. | |
| May 30, 2023 at 15:39 | comment | added | Rick James | @adamdrobniczak - Yes, there are lots of possible spelling errors. Soundex covers some things, but focuses on "sound" and the first few letters of each word. Fulltext chops off endings, but based on English. Single-letter insert/delete/change is another thing to tackle, but only handles a single letter. A simple transposition could be tackled. Levensthein tackles other things. | |
| May 30, 2023 at 8:48 | comment | added | adam drobniczak | yep, but you don't know in advance what the input query is, also there might be other kinds of problems with spelling | |
| May 26, 2023 at 15:01 | comment | added | Rick James | To be typo-tolerant, you need "like %xyz% OR like %xzy% OR like %yxz%" to handle a simple transposition with "y". | |
| May 26, 2023 at 8:25 | comment | added | adam drobniczak | Want to move away because of the operational overhead of running multiple storage systems. As to full text index, I just think it might be a bit wasteful to employ match query to filter 1000 rows out of 30_000_000, or even compare values between buckets and create this gigantic index while actually the bucket_id index reduces the data size by 99.99%. Wouldn't full text index with ngram parser achieve typo tolerance? I feel that this was the whole point of using full text index, otherwise could use regular index to narrow data down and combine it with "like %xyz%" query on the subset | |
| May 25, 2023 at 19:38 | comment | added | Rick James |
None of MySQL's offerings are really "typo tolerant". Why do you need to get away from ElasticSearch? When MATCH finds 1000 rows, it looks at only 1000 rows and delivers only 1000 to the next test (eg, to filter on bucket). No 30M-row scan. It is probably not practical if it finds 100K rows. What is the likely Match size?
|
|
| May 25, 2023 at 7:51 | comment | added | adam drobniczak | Ah i don't have the ability to easily try on the real dataset and worried that for anything smaller it might affect the query optimiser. I'm trying to decide the technology to use to replace existing elastic search implementation that does this. Given that I don't only need to be typo tolerant but also be able to write just a few characters inside a bigger data field, i think full text index is probably the way to go anyway. Just worried about the cost of running the index on the whole dataset across all the buckets while in reality nobody ever needs to query data across 2 different buckets | |
| May 24, 2023 at 19:41 | history | answered | Rick James | CC BY-SA 4.0 |