时间戳

首页 > 解决方案 > 在单独的表格中搜索产品的描述和关键字

mysql - 在单独的表格中搜索产品的描述和关键字

问题描述

我有两张 MySQL 表，一张保存产品，一张保存关键字；

    CREATE TABLE IF NOT EXISTS `products` (
      `id` int(6) unsigned NOT NULL,
      `title` varchar(200) NOT NULL,
      `description` varchar(200) NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;

    INSERT INTO `products` (`id`, `title`, `description`) 
    VALUES
      ('1', 'Product 1', 'Vacuum cleaner'),
      ('2', 'Product 2', 'Refrigerator'),
      ('3', 'Product 3', 'Mixer'),
      ('4', 'Product 4', 'Stereo');

    CREATE TABLE IF NOT EXISTS `keywords` (
      `keyid` int(6) unsigned NOT NULL,
      `productid` int(6) unsigned NOT NULL,
      `keyword` varchar(200) NOT NULL,
      PRIMARY KEY (`keyid`)
    ) DEFAULT CHARSET=utf8;

    INSERT INTO `keywords` (`keyid`, `productid`, `keyword`) 
    VALUES
      ('1', '1', 'white matte'),
      ('2', '1', '100W'),
      ('3', '3', 'shiny black'),
      ('4', '3', '15W'),
      ('5', '4', '350W');
ID 标题 描述
1 产品一 吸尘器
2 产品 2 冰箱
3 产品 3 混合器
产品编号 关键词
1 白色哑光
1 100W
3 闪亮的黑色
3 25W

有些产品会有关键字，有些则没有。

现在我想以搜索标题、描述和所有关键字的方式查询产品。

例如，我想搜索“白色 100W”，以便在这种情况下找到产品 1。

我将所有搜索词放在一个数组中并进行这种查询：

SELECT p.id
     , p.title 
      FROM products AS p 
  LEFT 
  JOIN keywords AS k 
    ON k.productID = p.ID 
 WHERE
     (p.title LIKE '%white%' 
    OR p.description LIKE '%white%' 
    OR k.keyword LIKE '%cleaner%') 
   AND 
     (p.title LIKE '%100W%' 
    OR p.description LIKE '%100W%' 
    OR k.keyword LIKE '%100W%') 
 GROUP 
    BY p.ID

更新：这是一个 SQLfiddle http://sqlfiddle.com/#!9/8114e9/2 ，它返回零行，因为在连接中只考虑了第一个关键字。我希望它浏览所有关键字（实际上通常是 20-30）。

解决这个问题的最有效方法是什么？我尝试查看 GROUP_CONCAT(all keywords) AS 关键字和 HAVING 关键字 LIKE ... 但是当没有关键字或我只是搜索部分描述时，这不起作用。

我还研究了一个UNION，基本上是在做

SELECT ... FROM products WHERE title LIKE ... OR description LIKE ...
UNION
SELECT ... FROM keywords WHERE keyword LIKE ...

但这似乎效率低得多，执行时间长。这里最有效的解决方案是什么？

标签： mysql

解决方案

LIKE以通配符开头的A不能使用任何索引。所以product无论如何你都会有一个全表扫描。因此，您使用析取析取的解决方案就足够了。

对于keywords您可以EXISTS与相关子查询一起使用。索引keywords (productid)或keywords (productid, keyword)可能有助于限制对LIKE真正属于产品的关键字的昂贵扫描。

SELECT p.id,
       p.title
       FROM products p
       WHERE <disjunction for p.title>
              OR
              <disjunction for p.description>
               OR EXISTS (SELECT *
                                 FROM keywords k
                                 WHERE k.productid = p.id
                                       AND (<disjunction for k.keyword>);

但可能想要完全重构并尝试使用全文索引和搜索功能。

推荐阅读