Let say that we have around 3M document of one page each. I want to import all the text in a MySQL database. For what I'm reading, I can add it in as a text field (65535 characters).
My challenge is this, we are receiving documents on a daily basis that is slightly or identical to one of the 3M documents we have. Is there a way or structure that can search a block of text in MySQL text when the input text is not identical? I need to know if we have one or multiple documents that are closely matching the one received. In a perfect world, I would like to have a result of the one that is closer in match to the search text...
The resulting mechanism to do this could include MySQL and Python as a solution. I can't find anywhere what could be the best approach to do this and that doesn't take forever to search.
[Edited 2020-09-29]
From what I have found for the moment, it looks like a good direction for that type of problem could be to use Elasticsearch as sabik pointed out.
[Edited 2020-12-14]
We finally have given a try to ElasticSearch and it's definitely a good solution.
One thing that we found not perfect is the fact that we want to search in the documents but if no document is close enough we wanted to have a similar score and it looks like this is not how it work. It always returns a document and the score is irrelevant. We will probably compare manually the best match to the searched document to have our own similarity score.
Some time ago I tried to do something like this with MySQL and then I found SQL SERVER FILESTREAM.
I save the document in a field of binary type varbinary(max), which takes up less space and the search is very fast.
Maybe now you can see if MySQL has something similar to FILESTREAM, I haven't researched it recently.
MySQL storing and searching for text