How to do multiple database search?

Question

PHP

All the good time of the day!
The essence of the question is, you need to somehow do a search through all the fields in the database for the string from the user.
We have a table with dots and fields (name, route, type_id, subtype_id)
The name and route fields are string fields, and type and subtype are links to related tables where there is (id, name).
And conditionally, if the user enters "point1 building", we must return him a point with name (point1) and type (building) from another table.
or the user enters "point1 residential", we must return him a point with name(point1) and subtype(residential) from another table.
In short, I think the essence is clear) the essence of the question is how to write such that it was all one request? and it worked well)
the only thing that came to mind, at the moment, is to make an additional field in the table in which, when creating a point, data about its name, type, subtype, etc. will be recorded. and the search is already conducted only on this field, but there is but, if suddenly the name of our sub type changes, then it will not change in this field, and it turns out that we will not find what we are looking for)
something like this) does anyone have any ideas?
thanks in advance to all!!

Answer 1

In short, the option with a search engine such as Sphinx or the like is not suitable, because my project is not currently located on a dedicated server and I do not have the rights to run various services.
got out of the situation so far

SELECT s.* ,st.`name` AS type_name ,  group_concat(DISTINCT benefits.name SEPARATOR ' ') as concat_bn, st.alias as spot_type_alias, benefits.alias as benefit_alias, CONCAT(" ", group_concat(DISTINCT benefits.id SEPARATOR ' ')  ," ")  as concat_bn_id,
  // для полей s.name , s.track, st.name , st.alias ,benefits.name , benefits.alias в каждой таблице создаем полнотекстовый индекс. 
  // 
  // тут считаем релевантность запроса
  MATCH(s.name , s.track) AGAINST('+$query') as score,
  MATCH(st.name , st.alias) AGAINST('+$query') as stcore,
  MATCH(benefits.name , benefits.alias) AGAINST('+$query') as bncore,
   (                // тут считаем расстояние до точки (тк я ищу точки ) (опционально)
                    6371 *
                    acos(cos(radians($lat)) *
                    cos(radians(s.lat)) *
                    cos(radians(s.lng) -
                    radians($lng)) +
                    sin(radians($lat)) *
                    sin(radians(s.lat)))
                    ) AS distance 
FROM `spots` s
// прикручиваем связь один к одному для поиска по типу из другой таблицы
JOIN `spot_type` st ON s.id_spot_type = st.id
// прикручиваем сводную таблицу для связи многие ко многим для поиска по типам плюшек на точке 
join spots_benefits
 on spots_benefits.spot = s.id
join benefits 
 on benefits.id = spots_benefits.benefit

where 
//собственно сам поиск
  MATCH(s.name, s.track) AGAINST('+$query')
  OR MATCH(st.name, st.alias) AGAINST('+$query')
  OR MATCH(benefits.name, benefits.alias) AGAINST('+$query')
  // групируем элементы из сводных таблиц в одно поле груп_конкат
group by s.id 
// ищем по только те что были добавлены в фильтр (опционально) типов 
HAVING  concat_bn_id like "% 1 %" AND concat_bn_id like '% 2 %' AND  s.id_spot_type = 2
// сортируем сначала по релевантности, а потом уже по расстоянию
ORDER BY (score + stcore + bncore) DESC, distance ASC

something like this ... the request, in principle, quickly processes (approximately 0.005 - 0.01) relatively of course, but for a database with 20k records
, if anyone has ideas for optimization, I will be glad to hear your opinion! )
thank you all

Answer 2

The vast majority of people implement a similar task in this way, in the forehead, losing useful information about data types, combining them into a text string.
But in some (and maybe frequent) cases, as for example described in the question, you can extract information from the query itself, which words belong to which data types, you just first need to look for words in reference books (related tables by type_id and subtype_id fields), and already armed with a list of filtered identifiers, look for them in the target table.
In this particular case, the profit may not be noticeable (you can more accurately rank the results by pulling out more relevant queries above), but for example, when queries are processed with non-text data types, for example, with dates, you can search directly for fields with them, for example the query contains the name of the month in a word, you can add to search queries a search by fields of the date type, such a full-text search cannot be easily provided, plus it is possible to implement logical expressions, time intervals, and so on.
This is more difficult, but in special complex cases it can be justified.
Full-text can be upgraded to support such cases by expanding the text string that is searched for by all variants of search queries, but this increase in resource costs, and noticeable but most importantly, does not allow better ranking of the results.