Как сделать множественный поиск по БД?

Question

[Alexey Gus]

Все доброго времени суток!
Суть вопроса в чем, нужно как-то сделать поиск по всем полям в бд по строке от пользователя.
У нас есть табличка с точками и полями(name, route, type_id,subtype_id)
Поле name и route это строковые поля , а type и subtype это ссылки на связанные таблицы где есть (id, name).
И условно если пользователь введет нам "точка1 здание", мы должны вернуть ему точку с name(точка1) и type(здание) из другой таблицы.
или пользователь введет нам "точка1 жилое", мы должны вернуть ему точку с name(точка1) и subtype(жилое) из другой таблицы.
короче суть думаю понятна) суть вопроса как написать такое чтобы это все был один запрос ? и работал он адекватно)
единственное что пришло в голову, на данный момент, это делать доп поле в таблице в которое при создании точки будут записываться данные о ее имени , типе , субтипе и тд . и поиск вести уже только по этому полю , но тут есть но , если вдруг название нашего суб типа поменяется, то в этом поле оно не измениться , и получается что мы не найдем искомое)
вот как-то так ) есть у кого какие идеи?
заранее всем благодарен!!)

Comments

[Ипатьев]

делать доп поле в таблице в которое при создании точки будут записываться данные о ее имени , типе , субтипе и тд . и поиск вести уже только по этому полю

поздравляю, вы изобрели полнотекстовый поисковый движок. Можно не писать свой, а взять готовый, Sphinxsearch или Elasticsearch

[Alexey Gus]

Роман Юрьевич Ипатьев, спасибо. )) Гляну )

Answer 1

[Alexey Gus]

Короче вариант с поисковым движком типа сфинкс или подобного не подходит , тк мой проект сейчас находится не на выделенном серваке и у меня нет прав запуска различных служб.
покачто вышел из ситуации как

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

как-то так... запрос в принципе быстро обрабатывает (примерно 0,005 - 0,01 ) относительно конечно , но для бд с 20к записей норм
если у кого есть идеи по оптимизации буду рад вашему мнению ! )
всем спасибо

Answer 2

[rPman]

Подавляющее большинство реализуют подобную задачу именно так, в лоб, теряя полезную информацию о типах данных, объединяя их в текстовую строку.

Но в некоторых (а может и частых) случаях, как к примеру описан в вопросе, из самого запроса можно вытащить информацию, какие слова к каким типам данных относятся, просто сначала нужно искать слова в справочниках (связанных таблицах по полям type_id и subtype_id), и уже вооружившись списком отфильтрованных идентификаторов, искать их в целевой таблице.

В данном конкретном случае профит может быть не заметным (можно более точно ранжировать результаты, вытаскивая более релеватные запросы выше), но к примеру когда обрабатываются запросы с не текстовыми типами данных, к примеру с датами, поиск можно вести напрямую по полям с ними, например в запросе есть название месяца словом, можно добавить к поисковым запросам поиск по полям типа дата, такой поиск полнотекстовый обеспечить легко не сможет, плюс есть возможность реализовывать логические выражения, временные интервалы и прочее прочее.

Это сложнее, но в особых сложных случаях это может быть оправдано.

Полнотекстовый можно проапгрейдить до поддержки подобных случаев, расширяя текстовую строку, по которой идет поиск всеми вариантами поисковых запросов, но это повышение затрат ресурсов, причем заметное но главное, не позволяет лучше ранжировать результаты.