How to make a search for individual words scattered throughout the text?

Question

MySQL

There is a site with articles.
Let's say there is a search query "how to write an optimized search on the site"
There is such a text "Do you want to write a search on the site? Moreover, what would it be optimized for? You hit the mark! ..."
It turns out that our query should find this text (and still others satisfying the condition), but not by the direct occurrence of the substring, but by individual words scattered throughout the text.
As I understand it, you need to split the query string into an array of words
['how', 'write', 'optimized', 'search', 'on', 'site']
and then work with it.
How to write a query to the Mysql database in order to implement such a search? And in general, I think right

Answer 1

For such purposes, they usually use a search engine that integrates with MySQL:

• Sphinx
  
• elasticSearch
  
• Apache Solr
  
• ...
  

Answer 2

Well, for MS SQL I will write this, the function of splitting a string by a separator character is written in 10 lines, someone at our work wrote it, the code looked, nothing complicated. We build the sql query longer in the code (@sql varchar(max)), assign it in the loop and like % + select value from array where id = @counter_in_loop) + %. Run exec @sql. For mysql - it's probably also possible, I haven't worked with it myself, but the database class is the same, there should be possibilities in the syntax.
And then optimized not like optimized))))
Normal search is definitely not done in sql. You can make some kind of crocodile out of crutches. Unfortunately, I can't tell you how to do a normal search, but I would look for some information, some stories "how we did a cool / normal search.
There is a concept of full-text search, it seems to be in Mysql and it solves beautifully what I wrote at the beginning, but does not solve the problem of the form (cases of declensions, etc.) of words.

Answer 3

for MS SQL, I will write this, the function of splitting a string by a separator character is written in 10 lines

There is a ready-made STRING_SPLIT .