Find posts by more than one keyword

[codegold79]

Currently, even if a user sends in multiple keywords to find a post, only the first keyword will be used to search for a post. Be able to query for more than one keyword.

Be sure to search the keywords in both the post title and body.

A/C

• [ ] Determine the number of keywords allowed to search. Discuss with team before making decision.
• [ ] Determine how to use the keywords in combination. For example, should a post including any keyword be found? Do all keywords need to be in the body or title? Should a more sophisticated algorithm or library be used? Discuss with team before making decision.
• [ ] Enable search by multiple keywords.
• [ ] Remove the TODO comment at https://github.com/OpenEugene/openboard/blob/73e3bf4cb16642bc754c08f5ef5d400dbfdf8213/back/internal/postsvc/internal/postdb/qrypost.go#L111
• [ ] Create tests to ensure the find post query is working as intended.

[appins]

Here are some notes on fulltext in MySQL:

• There are certain things we can do with natural text searching that we cannot easily do with like, such as excluding English words automatically (e.g. the).
• fulltext is faster than like, especially with several like statements union'd together. The main tradeoff is space. See This StackOverflow question.
• fulltext is a form of indexing that stores the existence of certain words in certain posts, along with their locations and frequencies. Indexing cannot improve the performance of %like or %like% statements, but could improve the performance of like% statements.
• It seems that fulltext uses around 65% of the data that it indexes. It is highly dependent on the number of words separated by whitespace. See this StackOverflow question.
• We would likely want a fulltext column (or index) for body and another column for the title.
• We can drop and add the fulltext column whenever. It is automatically calculated.

My thoughts:

• This is probably the best option. like is both slower and produces worse results, while fulltext just costs more space.
• Someone pointed out that while there are some better solutions for specific scenarios, fulltext is the best option 95% of the time.
• We should use it if we can afford a database being 1.25 to 1.75 times bigger.

cc @daved @codegold79: What do y'all think?