Я не уверен, что правильно понял, но вот мои 2 цента.

Из того, что я вижу, проблема в том, что у вас есть 2 таблицы с очень разными макетами, поэтому я предположу, что вы хотите основывать полнотекстовый поиск на этих полях:

• для магазинов : название, описание и ключевые слова
• для элементов магазина : имя и описание

Решение 1: Согласованность компоновки - не использует индекс...

Если бы вы могли как-нибудь измените названия ваших столбцов для shopitems, это сразу же станет намного проще.

Select id From
(Select id, text1, text2, text3 From table1
 UNION
 Select id, text1, text2, text3 From table2)
Where MATCH(id, text1, text2, text3) AGAINST('keyword1 keyword2 keyword3')

Однако я могу понять, что было бы непрактично менять все, что уже существует. Обратите внимание, что при использовании псевдонимов добавление третьего (фиктивного) текстового столбца в shopitems может помочь.

Решение 2: Последующая обработка

Я должен отметить, что вычисленное значение действительно может быть возвращено (и, следовательно, использовано). Поэтому вы можете создать временную таблицу с такой ценностью! Обратите внимание, что если вы хотите вернуть "заголовок" и "описание", оба столбца должны иметь один и тот же тип, с которым нужно работать единообразно...

Select id, title, description From
(
 Select id, title, description, MATCH(id, title, description, keywords) AGAINST('dummy') As score
        From shops
        Where MATCH(id, title, description, keywords) AGAINST('dummy')
 UNION
 Select id, name As title, description, MATCH(id, name, description) AGAINST('dummy') As score
        From shopitems
        Where MATCH(id, name, description) AGAINST('dummy')
)
ORDER BY score DESC

Я понятия не имею о производительности этого запроса, хотя мне интересно, будет ли mysql оптимизировать двойной вызов для СОПОСТАВЛЕНИЯ/СОПОСТАВЛЕНИЯ в каждом из выбранных (я надеюсь, что это так).

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

В любом случае, я надеюсь, что это помогло вам.

Я предлагаю вам первый вариант. Избыточность не всегда является злом.

Поэтому я бы сделал такую таблицу:

CREATE TABLE search_results
(
   ...
   `searchable_shop_info` VARCHAR(32),
   `searchable_shopitem_info` TEXT
   FULLTEXT KEY `searchable` (`searchable_shop_info`, `searchable_shopitem_info`)
) Engine=MyISAM;

Тогда вы все равно можете использовать SELECT * FROM search_results WHERE MATCH (searchable_shop_info,searchable_shopitime_info для поиска) AGAINST ('search query string');

Если я правильно понимаю ваши вопросы, ответ очень прост:

1. Не меняйте дизайн. Все в полном порядке. Вот как это должно быть.
2. Выполните объединенный запрос следующим образом:

SELECT * FROM shops
LEFT OUTER JOIN shopitems ON (shopitems.shopid = shops.id)
WHERE 
    MATCH (shops.title, shops.description, shops.keywords,
           shopitems.name, shopitems.description) 
    AGAINST ('whatever text')

Я бы пошел за СОЮЗ. В этом и заключается цель данного заявления.

Я бы выбрал ваш первый вариант, создав отдельную таблицу поиска.

Мы сделали это однажды, когда нам нужно было выполнить поиск данных в нескольких системах SOA.

Преимущества этого подхода заключаются в следующем:

• более быстрое реагирование на поисковые запросы
• больший контроль над организацией результатов поиска

Недостатками являются:

• более медленное время сохранения данных, так как они должны быть записаны в двух местах
• дополнительное пространство, используемое для хранения данных

Хм, может быть, ты можешь использовать союз? как

create table search1 (
    title varchar(12), 
    relavency tinyint unsigned
);

create table search2 (
    title varchar(12), 
    relavency tinyint unsigned
);

insert into search1 values (substring(md5(rand()), 1, 12), (rand()*100)), 
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100));

insert into search2 values (substring(md5(rand()), 1, 12), (rand()*100)), 
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100)),
(substring(md5(rand()), 1, 12), (rand()*100));

(select *, 'search1' as source from search1) 
union (select *, 'search2' as source from search2) 
order by relevancy desc;

Выберите свои строки и рассчитайте релевантность в соответствии с нормой, а затем объедините результаты. я не знаю, правильно ли я вас понял, потому что, похоже, никто не думает о союзе?

ОБНОВЛЕНИЕ 1:

Хорошо, я уже перечитал ваш вопрос и комментарий... я думаю

1) должен ли я изменить свой дизайн? я подумываю о том, чтобы создать отдельную таблицу под названием результаты поиска, которая будет содержать данные как из МАГАЗИНОВ, так и Таблица ТОВАРОВ ДЛЯ ПОКУПОК. однако это означает, что у меня есть некоторое дублирование данных.

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

CREATE VIEW viewSearch (Title, Relavency, SourceTable) AS 
(SELECT title, relavency, 'search1' as source FROM search1
ORDER BY relavency DESC
LIMIT 10)
UNION 
(SELECT title, relavency, 'search2' as source FROM search2
ORDER BY relavency DESC
LIMIT 10)
ORDER BY relavency DESC 
LIMIT 10;

2) должен ли я сохранить свой текущий дизайн? если да, то как я могу отсортировать результаты поиска по релевантности в 2 разных таблицах?

По SQL/Представление выше вы можете. в основном, разместив

...
ORDER BY relavency DESC 
LIMIT 10

Мне любопытно. это означает, что мне нужно запускать этот запрос КАЖДЫЙ раз для любого поиска вход. потому что разные входные данные будут иметь разные оценки релевантности.

Я действительно не понимаю, что ты имеешь в виду? если бы вы сейчас искали между 2 таблицами, не сделали бы вы 2 отдельных SQL-запроса (по 1 для каждой таблицы)? или, если бы вы выбрали результаты в 1 таблицу, это все равно... на самом деле 3 запроса (2 для выбора в таблице результатов, затем 1 для запроса).

Я также добавил ПОРЯДОК и ОГРАНИЧЕНИЕ в каждый ВЫБОР, чтобы ускорить процесс, получив меньше записей. затем ЗАКАЖИТЕ и ОГРАНИЧЬТЕ еще раз в целом.

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

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

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

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