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

• Все индексы в 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 . В shopitems 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 настолько дорогостоящим, что рекомендуемым решением является создание одного индекса для более старых, архивных данных и другого меньшего индекса для последних данных, которые, скорее всего, будут обновляться. Тогда каждый поиск должен запускать два запроса, против двух отдельных индексов. И если ваши данные естественным образом не поддаются изменению прежних данных, то вы, возможно, не сможете воспользоваться этим трюком.

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

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

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

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

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

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

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

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

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

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

Решение 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 оптимизировать двойной вызов 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 ( ) AGAINST ('search query string');

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

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

 ВЫБЕРИТЕ * ИЗ магазинов
 LEFT OUTER JOIN shopitems ON (shopitems.shopid = shops.id)
 ГДЕ 
     MATCH (shops.title, shops.description, shops.keywords,
            shopitems.name, shopitems.description) 
     ПРОТИВ («любой текст»)

Я бы пошел на СОЮЗ. Это и есть цель заявления.

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

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

Преимущества такого подхода:

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

Недостатки:

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

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

 создать таблицу search1 (
     название varchar (12), 
     Радиус действия tinyint без знака
 );

 создать таблицу search2 (
     название varchar (12), 
     Радиус действия tinyint без знака
 );

 вставлять в значения search1 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)), 
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100));

 вставлять в значения search2 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)), 
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100)),
 (подстрока (md5 (rand ()), 1, 12), (rand () * 100));

 (выберите *, 'search1' в качестве источника поиска1) 
 union (выберите *, 'search2' в качестве источника поиска2) 
 порядок по релевантности desc; 

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

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

хорошо я перечитываю ур вопрос и комментарий уже … я думаю

1) должен ли я изменить свой дизайн? я думаю о наличии отдельной таблицы, называемой результатами поиска, которая будет содержать данные из обеих таблиц SHOPS и SHOPPRODUCTS. однако это означает, что у меня есть дублирование данных.

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

  CREATE VIEW viewSearch (Title, Relavency, SourceTable) AS 
 (SELECT title, relavency, 'search1' как источник FROM search1
 ORDER BY relavency DESC
 ПРЕДЕЛ 10)
 UNION 
 (SELECT title, relavency, 'search2' как источник FROM search2
 ORDER BY relavency DESC
 ПРЕДЕЛ 10)
 ORDER BY relavency DESC 
 LIMIT 10; 

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

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

  ...
 ORDER BY relavency DESC 
 LIMIT 10 

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

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

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

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

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

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

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