Can you clarify how / why it would be difficult?

Suppose we kept the id of the original message. When we run a full text search on this table, we only get the processed message, so we need to fetch the full message content form the messages table. To fetch the most recent edit, we would have to query for all messages with id or target_message matching the id and pick the most recent one.
Me and @tomek discussed this and figured that if we save the id of the edited message, then we can very easily fetch this and the original message, and send them to the client (in some single data structure), and they will have all the information about this message that they need.

For the approach with changing the id in message_id column (and using the fact that we keep the old id as the primary key) the query would be something like:

SELECT * FROM messages m JOIN search s ON s.original_message_id = m.id OR m.id = s.message_id WHERE MATCH(s.processed_content) AGAINST(query);

And we would get the two messages for each match- the original and the most recent edit.

But for the approach where we keep only the old id we would need to either run two queries to get the original and the newest edit separately, or have the query return all edits and handle that in js, or make one query return all originals but only the newest edits (I can only think of a way to do that that requires a nested SELECT:

SELECT * FROM messages m JOIN search s ON s.original_message_id = m.id OR m.target_message = s.original_message_id WHERE MATCH(s.processed_content) AGAINST('friend') AND (m.target_message IS NULL OR m.time=(SELECT MAX(mm.time) FROM messages mm WHERE mm.target_message = m.target_message));

)

Yes

It is a modifier on an existing column. It can be given a name, but this also works - it gives the index the name of the column. This is how it's done in docs. Should I give it a name anyway?

I suppose I should use the same value as for messages table, yes. utf8mb4 can store 4-byte chars, that include musical symbols, some historic alphabets, some emoji’s and some other symbols.

I tested this diff as described in the test plan (that's how I encountered the problem with migrations), but I think I forgot to check the effects of this this last statement. Sorry

Although the natural library treats everything not in [a-zA-Z0-9] as a separator, so emojis are not indexed (source code)

I did some playing around with V8 and generally confirmed @inka's research. I didn't get to the point where I was able to precisely identify that \W matches [^A-Za-z0-9_]... instead, I gave up early after I found that the behavior isn't really what we want, eg.:

However, after some Googling and playing around I was able to get Intl.Segmenter to handle this in a much better way:

@inka, do you think we could use Intl.Segmenter for tokenization, and then the natural library for stemming? I'm open to patch-package on natural if necessary (but ideally perhaps we can avoid it).

There also seem to be a variety of tokenizers available in the natural library. Can you clarify:

1. What is the default tokenization algorithm used by natural.PorterStemmer.tokenizeAndStem? I'm guessing it's AggressiveTokenizer since you linked that, but I want to make sure.
2. Have you looked into the other tokenization algorithms? Are any of them better than AggressiveTokenizer for the string I shared in my screenshot above? Are any of them better than Intl.Segmenter?

It seems we always create indexes in separate statements in setup-db.js. I followed this convention here. If this is incorrect please let me know.

natural.PorterStemmer.tokenizeAndStem uses AggressiveTokenizer by default (PorterStemmer gets this function from Stemmer that it extendes, and Stemmer uses AggressiveTokenizer).

From the other tokanizers the only one that looks potentially useful for us is RegexpTokenizer, that allows to define own regex.

const tokenizer = new natural.RegexpTokenizer({
      pattern: /([A-Za-zÀ-ÿ-'"]+|[0-9._]+|.|!|\?|:|;|,|-)/iu,
    });
    console.log(tokenizer.tokenize(text));

produces

[ 'Hello', 'test', "it's", 'working', '😄', 'test' ]

But it would be a bit difficult to have it parse it's as it's and 'hello' as hello. Generally we would have to come up with a good regex.

The disadvantage of Intl.Segmenter is that there seems to be some problem with types: Intl is typed in flow builtin definitions, but it does not contain Segmenter https://github.com/facebook/flow/blob/main/lib/intl.js
But it works, so if I work around the flow problem it should be fine.

(sorry, was supposed to answer elsewhere, going to comment on D7077 as well )