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

• Все индексы в MySQL могут ссылаться только на столбцы в одной базовой таблице. Вы не можете создать полнотекстовый индекс, который индексирует несколько таблиц.
• Вы не можете определить индексы для представлений, только для базовых таблиц.
• Запрос MATCH() к полнотекстовому индексу должен соответствовать всем столбцам полнотекстового индекса в порядке, объявленном в индексе.

Я бы создал третью таблицу для хранения контента, который вы хотите индексировать. Нет необходимости хранить это содержимое избыточно — храните его исключительно в третьей таблице. Это заимствует концепцию «общего суперкласса» из объектно-ориентированного проектирования (насколько мы можем применить его к проектированию СУБД).

CREATE TABLE Searchable (
  `id` SERIAL PRIMARY KEY,
  `title` varchar(100) default NULL,
  `description` text,
  `keywords` text,
  `url` varchar(255) default '',
  FULLTEXT KEY `TitleDescFullText` (`keywords`,`title`,`description`,`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `shopitems` (
  `id` INT UNSIGNED NOT NULL,
  `ShopID` INT UNSIGNED NOT NULL,
  `ImageID` INT UNSIGNED NOT NULL,
  `pricing` varchar(45) NOT NULL,
  `datetime_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES Searchable (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `shops` (
  `id` INT UNSIGNED NOT NULL,
  `owner_id` varchar(255) default NULL,
  `datetime_created` datetime default NULL,
  `created_by` varchar(255) default NULL,
  `datetime_modified` datetime default NULL,
  `modified_by` varchar(255) default NULL,
  `overall_rating_avg` decimal(4,2) default '0.00',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES Searchable (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Обратите внимание, что единственная таблица с ключом автоинкремента теперь Searchable. В таблицах shops и shopitems используется ключ с совместимым типом данных, но без автоинкремента. Таким образом, вы должны создать строку в Searchable, чтобы сгенерировать значение id, прежде чем вы сможете создать соответствующую строку в shops или shopitems.

Я добавил FOREIGN KEY объявлений для наглядности, хотя MyISAM молча проигнорирует эти ограничения (и вы уже знаете, что вы должны использовать MyISAM для поддержки полнотекстового индексирования).

Теперь вы можете искать текстовое содержимое как shops, так и shopitems в одном запросе, используя один полнотекстовый индекс:

SELECT S.*, sh.*, si.*,
  MATCH(keywords, title, description, url) AGAINST('dummy') As score
FROM Searchable S
LEFT OUTER JOIN shops sh ON (S.id = sh.id)
LEFT OUTER JOIN shopitems si ON (S.id = si.id)
WHERE MATCH(keywords, title, description, url) AGAINST('dummy')
ORDER BY score DESC;

Конечно, для данной строки в Searchable должна совпадать только одна таблица, либо магазины, либо товары, и эти таблицы имеют разные столбцы. Таким образом, либо sh.*, либо si.* в результате будет NULL. Вы должны отформатировать вывод в своем приложении.

Несколько других ответов предложили использовать Sphinx Search. Это еще одна технология, дополняющая MySQL и добавляющая более сложные возможности полнотекстового поиска. У него отличная производительность для запросов, поэтому некоторые люди были очарованы им.

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

Что касается вашего комментария: вот выдержка из документации Sphinx Search о живых обновления индекса:

Нередки ситуации, когда общий набор данных слишком велик для частой переиндексации с нуля, а количество новых записей довольно мало. Пример: форум с 1 000 000 заархивированных сообщений, но только 1 000 новых сообщений в день.

В этом случае «живые» (почти в реальном времени) обновления индекса могут быть реализованы по так называемой схеме «основной + дельта».

Идея состоит в том, что, поскольку обновление индекса Sphinx Search обходится дорого, их решение состоит в том, чтобы сделать индекс, который вы обновляете, как можно меньше. Так что только самые последние сообщения на форуме (в их примере), тогда как большая история архивных сообщений на форуме никогда не меняется, поэтому вы создаете второй, более крупный индекс для этой коллекции один раз. Конечно, если вы хотите выполнить поиск, вы должны запросить оба индекса.

Периодически, скажем, раз в неделю, «последние» сообщения форума будут считаться «архивными», и вам придется объединить текущий индекс последних сообщений с архивным индексом и начать меньший индекс заново. Они отмечают, что объединение двух индексов Sphinx Search более эффективно, чем переиндексация после обновления данных.

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

Возьмем, к примеру, вашу базу данных: у вас есть магазины и товары. Как вы можете разделить их на строки, которые никогда не меняются, по сравнению с новыми строками? Любым магазинам или продуктам в каталоге должно быть разрешено обновлять свое описание. Но поскольку это потребует перестроения всего индекса Sphinx Search каждый раз, когда вы вносите изменения, это становится очень дорогостоящей операцией. Возможно, вы поставите изменения в очередь и примените их пакетно, перестраивая индекс раз в неделю. Но попробуйте объяснить продавцам магазина, почему небольшое изменение в описании их магазина вступит в силу только в воскресенье вечером.

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

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

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

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

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

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 двойной вызов MATCH / AGAINST в каждом из Selects (я надеюсь, что это так).

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

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

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

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

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) я должен изменить свой дизайн? Я думаю о том, чтобы иметь отдельную таблицу под названием результаты поиска, которая будет содержать данные как из таблицы SHOPS, так и из таблицы SHOPPRODUCTS. однако это означает, что у меня есть дублирование данных.

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

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) должен ли я сохранить свой текущий дизайн? если да, то как я могу получить результаты поиска, отсортированные по релевантности в двух разных таблицах?

с помощью SQL/View выше вы можете. в основном путем размещения

...
ORDER BY relavency DESC 
LIMIT 10

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

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

Я также добавил ORDER BY & LIMIT в каждый SELECT, чтобы ускорить процесс за счет получения меньшего количества записей. затем ORDER BY & LIMIT еще раз в целом.

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

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

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

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