MySQL - Evil Query 2

[Иван Шумов]

знаете, когда-нибудь прийдется создавать отдельный раздел на базы данных)

в общем если кому интересно как можно сходить с ума - предлагаю изврат под названием полнотекстовый релевантный поиск средствами mysql по набору таблиц. Если кто закритикует - буду только рад.

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

SET @search_query = 'ГК201';
SET @site_url = 'http://example.com';
(
SELECT 
CONCAT(@site_url,'/news/open/',news.id,'/') as url,
news.title as title,
news.text as snippet,
MATCH (news.title) AGAINST (@search_query) + MATCH (news.text) AGAINST (@search_query) as rel
FROM ss_news_rus as news WHERE MATCH (news.title, news.text) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION 
(
SELECT
CONCAT(@site_url,'/aboutus/') as url,
'' as title,
about.txt as snippet,
MATCH (about.txt) AGAINST (@search_query) as rel
FROM ss_about_rus as about  WHERE MATCH (about.txt) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION 
(
SELECT
CONCAT(@site_url,'/history/') as url,
'' as title,
history.txt as snippet,
MATCH (history.txt) AGAINST (@search_query) as rel
FROM ss_history_rus as history  WHERE MATCH (history.txt) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION
(
SELECT 
CONCAT(@site_url,'/events/open/',events.id,'/') as url,
events.title as title,
events.text as snippet,
MATCH (events.title) AGAINST (@search_query) + MATCH (events.text) AGAINST (@search_query) as rel
FROM ss_events_rus as events WHERE MATCH (events.title, events.text) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION
(
SELECT 
CONCAT(@site_url,'/publications/open/',publications.id,'/') as url,
publications.title as title,
publications.text as snippet,
MATCH (publications.title) AGAINST (@search_query) + MATCH (publications.text) AGAINST (@search_query) as rel
FROM ss_publications_rus as publications WHERE MATCH (publications.title, publications.text) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION 
(
SELECT
CONCAT(@site_url,'/',scatalog.prefix,'/') as url,
scatalog.title as title,
scatalog.text as snippet,
MATCH (scatalog.text) AGAINST (@search_query) + MATCH (scatalog.title) AGAINST (@search_query) as rel
FROM ss_simple_catalog_rus as scatalog  WHERE MATCH (scatalog.title, scatalog.text) AGAINST (@search_query IN BOOLEAN MODE)
)
UNION 
(
SELECT
CONCAT(@site_url,'/oscillators/',oscillators.group,'/',oscillators.id,'/') as url,
oscillators.model as title,
'' as snippet,
MATCH (oscillators.model) AGAINST (@search_query) as rel
FROM ss_catalog_rus as oscillators  WHERE MATCH (oscillators.model) AGAINST (@search_query IN BOOLEAN MODE)
)
ORDER BY rel DESC