php - 如何实现搜索2个不同的表数据？

Question

使用mysql和PHP
我已经在使用MATCH-AGAINST子句了。
在单独的桌子上工作正常。就像我想在商店的桌子上搜索一样。没问题。
我想要的是能够在一个结果页面中搜索和显示来自不同表的结果。
如果我打“巧克力衣服”
我可以得到以下4个结果：
Shop1结果
ShopItem1结果
ShopItem2结果
Shop2结果
当然，最相关的结果应该排在第一位。
我有很多问题。设计和实现
1）我应该改变我的设计吗？我正在考虑使用一个名为search results的单独表，它将包含来自SHOPS和SHOPPRODUCTS表的数据。但这意味着我有一些重复数据。
2）我应该保留当前的设计吗？如果是的话，那我到底怎么才能在两个不同的表中得到按相关性排序的搜索结果呢？
我看到Rottentomotoes把他们的搜索结果分成不同的组。但是，我们希望搜索结果不受不同类型的限制，特别是当我们有分页时，分页将更加难以按用户界面导航。
http://www.rottentomatoes.com/search/full_search.php?search=girl
或者那是最好的出路？
我希望有人能给我这类事情的指导，特别是如果你有经验，产生搜索结果似乎是多个表。
由于需要，我将把表结构放在这里

CREATE TABLE `shopitems` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ShopID` int(10) unsigned NOT NULL,
  `ImageID` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `pricing` varchar(45) NOT NULL,
  `datetime_created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Table structure for table `shops` */

DROP TABLE IF EXISTS `shops`;

CREATE TABLE `shops` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `description` text,
  `keywords` text,
  `url` varchar(255) default '',

  `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`),
  FULLTEXT KEY `url` (`url`),
  FULLTEXT KEY `TitleDescFullText` (`keywords`,`title`,`description`,`url`)
) ENGINE=MyISAM AUTO_INCREMENT=3051 DEFAULT CHARSET=utf8;

我打算搜索shopproducts表的description和name列。
但正如你所看到的，它还没有实现。
尽管对商店的搜寻已经开始了。

Answer 1

这里有几个“游戏规则”，你必须记住才能解决这个问题。你可能已经知道这些了，但是把它们说清楚可能有助于其他读者的确认。
MySQL中的所有索引只能引用单个基表中的列。不能生成跨多个表索引的全文索引。
不能为视图定义索引，只能为基表。
对全文索引的查询必须按照索引中声明的顺序与全文索引中的所有列匹配。
我将创建第三个表来存储要索引的内容。不需要冗余地存储这些内容——只将其存储在第三个表中。这借用了面向对象设计中“公共超类”的概念（只要我们可以将其应用于RDBMS设计）。

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;

注意，现在只有一个带有自动递增键的表 MATCH()。表 Searchable和 shops使用具有兼容数据类型的键，但不使用自动递增键。因此，必须在 shopitems中创建一行以生成 Searchable值，然后才能在 id或 shops中创建相应的行。
我已经添加了 声明用于说明目的，即使MyISAM将无声地忽略这些约束（并且您已经知道必须使用MyISAM支持全文索引）。
现在，您可以使用单个全文索引在单个查询中搜索 shopitems和 FOREIGN KEY的文本内容：

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;

当然，对于 shops中的给定行，只有一个表应该匹配，shops或shoppitems，并且这些表有不同的列。因此 shopitems或 Searchable在结果中将为空。在应用程序中格式化输出取决于您。
其他一些答案建议使用 Sphinx Search。这是对MySQL的补充，并增加了更复杂的全文搜索功能。它有很好的查询性能，所以有些人已经被它迷住了。
但是创建索引，特别是增量添加到索引中是昂贵的。事实上，更新Sphinx搜索索引的成本非常高，建议的解决方案是为较旧的存档数据创建一个索引，并为更可能更新的最新数据创建另一个较小的索引。然后每个搜索都必须对两个独立的索引运行两个查询。如果你的数据不能自然地适应老数据不变的模式，那么你可能无论如何也不能利用这个技巧。
回复您的评论：以下是 Sphinx Search documentation关于索引实时更新的摘录：
经常会有这样的情况
总数据集太大，无法
经常从头开始重新编制索引，但是
新记录的数量相当少。
例如：一个拥有1000000美元的论坛
已存档的帖子，但只有1000个新的
每天发帖。
在这种情况下，“live”（几乎是真实的
时间）索引更新可以是
使用所谓的
“主要+三角洲”计划。
他们的想法是，由于更新Sphinx搜索索引的成本很高，所以他们的解决方案是使您更新的索引尽可能小。因此，只有最近的论坛帖子（在他们的例子中），而存档的论坛帖子的更大历史永远不会改变，所以您为该集合构建第二个更大的索引一次。当然，如果要进行搜索，必须同时查询两个索引。
周期性地，比如说一周一次，“最近”的论坛消息会被认为是“存档的”，你必须将最近帖子的当前索引合并到存档的索引中，然后重新开始较小的索引。他们确实指出，在更新数据之后，合并两个狮身人面像搜索索引比重新索引更有效率。
但我的观点是，并不是每一个数据集都会自然而然地陷入这样一种模式：拥有一组永不更改的存档数据，而不是最近频繁更新的数据。
以你的数据库为例：你有商店和购物用品。如何将这些行分成从不改变的行与新行？应允许目录中的任何商店或产品更新其说明。但由于每次进行更改时都需要重建整个Sphinx搜索索引，因此这将成为一个非常昂贵的操作。也许您应该将更改排队并成批应用，每周重新构建一次索引。但试着向店主解释一下，为什么对店面描述的细微改动要到周日晚上才能生效。