Поиск по двум связанным таблицам

Добрый день!

У меня есть две таблицы "Улицы" и "Строения". Строения привязаны к "Улицам" Необходимо осуществить поиск нужного строения имея поисковой запрос в виде текста(если поисковой запрос не точен - можно несколько результатов).
У строк есть так же "алиасы", те разные вариации написания(title: Чапаева, sub_titles: Кирова|Пушкина), те улица может иметь синонимы в названии.(формат хранения алиасов может быть любой, как вы скажете так и буду хранить).

Например: "Чапаева 14", "Чапаева", "улица Чапаева 14", "14 Чапаева" итд

`streets` (
`street_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`sub_titles` varchar(255) DEFAULT NULL,
PRIMARY KEY (`street_id`)
);
`locations` (
`location_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`street_id` smallint(5) unsigned DEFAULT NULL,
`title` varchar(100) NOT NULL,
PRIMARY KEY (`location_id`)
);

Нужно получить идентификаторы строений подходящих под поисковой запрос.

Пригодится любая помощь и советы. Даже просто описание запроса на "человеческом" языке, попробую если что сам его составить.

хм интересненько
во первых надо title и sub_titles перенести в отдельную таблицу один к многим street_title(тут рекомендую узнать что есть такое нормализация)
id, street_id, title
тогда сможем джойнить в запросе

по поводу текста
разбиваем текст на слова и ищем каждое слово в street_title
если слов несколько можно заюзать UNION
числа можно сразу отбросить еще в пыхе чтоб зря не нагружать запрос
пример "улица Чапаева 14"

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

По поводу запроса в принципе мне понятно. А будет ли это эффективно если человек не допишет до конца название улицы? Например "Чапа", ведь у нас строгое сравнение.
Я так понимаю нужно использовать REGEXP. Как во второй запрос его добавить?

Огромное спасибо за ваш совет. Очень помогли! "Спасибо!" уже улетело)

(Отредактировано автором: 10 Октября, 2017 - 15:44:59)

можно
но тогда отпадает индексация
вариант использовать префиксный LIKE

LIME, спасибо огромное!

И тогда получается из этого результата уже ищем по таблице с номерами домов(locations/там я тоже уберу sub_titles) нужный нам номер дома если имеется и его выдаём как нужный результат(первым в списке "советов" пользователю).

(Отредактировано автором: 10 Октября, 2017 - 15:55:51)

а если по хорошему я бы заюзал поисковый движек
проще всего elastic search
тогда никаких проблем ни со словами ни с неполным словом и даже морфология работает(чапаев, чапаеву, недочапаев) все найдет
я так делаю в своих проектах но возможно для тебя это будет оверхед
скорее будет оверхед но знай что такое есть
(Добавление)
можно и первый если у нас нет неполного слова
а если слово например "улица Красного" чегото там? первое попавшееся отдашь?
погугли автокомплит
jqueryui autocomplit обычно юзают
дешево и сердито
ajax можно прикрутить
(Добавление)
можно еще одним джойном добавить
думал догадаешься

LIME, кстати интересная идея. Пойду читать за него. Время позволяет, да и в будущем пригодится 100%.

Ещё раз спасибо за помощь! Всего хорошего!