分面搜索 (solr) 与通过 PHP 进行的旧过滤? [英] Faceted Search (solr) vs Good old filtering via PHP?
问题描述
我计划在我的电子商务商店中设置过滤系统(优化您的搜索).您可以在此处查看示例:http://www.bettymills.com/shop/product/find/Air+and+HVAC+Filters
I am planning on setting up a filter system (refine your search) in my ecommerce stores. You can see an example here: http://www.bettymills.com/shop/product/find/Air+and+HVAC+Filters
PrestaShop、OpenCart 和 Magento 等平台具有所谓的分层导航.
Platforms such as PrestaShop, OpenCart and Magento have what's called a Layered Navigation.
我的问题是，与使用 Solr 或 Lucene 等平台进行分面导航相比，Magento 或 PrestaShop 等平台中的分层导航有什么区别.
My question is what is the difference between the Layered Navigation in platforms such as Magento or PrestaShop in comparison to using something like Solr or Lucene for faceted navigation.
是否可以仅通过 php 和 mysql 实现类似的结果?
Can a similar result be accomplished via just php and mysql?
非常感谢详细的解释.
推荐答案
分层导航 == 分面搜索.
它们是同一个东西，但 Magento 和 al 使用不同的措辞，可能是为了吸引眼球.据我所知，Magento 支持 Solr 分面搜索或 MySQL 搜索.主要区别在于性能.
Layered Navigation == Faceted Search.
They are the same thing, but Magento and al uses different wording, probably to be catchy. As far as I know, Magento supports both the Solr faceted search or the MySQL one. The main difference is the performance.
在 MySQL 中进行分面搜索需要您连接表，而 Solr 会自动索引文档分面以进行过滤.您通常可以在普通硬件上使用 Solr(对于多方面搜索查询 <100 毫秒)实现快速响应时间.虽然 MySQL 进行相同的搜索需要更长的时间，但可以使用索引对其进行优化以实现类似的响应时间.
To do faceted search in MySQL requires you to join tables, while Solr indexes the document facets automatically for filtering. You can generally achieve fast response times using Solr (<100ms for a multi-facet search query) on average hardware. While MySQL will take longer for the same search, it can be optimized with indexes to achieve similar response times.
Solr 的缺点是它需要您配置、保护并在您的服务器上运行另一个服务.根据您的配置(Tomcat、jetty 等)，它也可能占用大量 CPU 和内存.
The downside to Solr is that it requires you to configure, secure and run yet another service on your server. It can also be pretty CPU and memory intensive depending on your configuration (Tomcat, jetty, etc.).
您需要一个特定的数据库架构，但它是可行的.这是一个简单的例子:
You need a specific database schema, but it's feasible. Here's a simple example:
产品
+----+------------+
| id | name |
+----+------------+
| 1 | blue paint |
| 2 | red paint |
+----+------------+
分类
+----+----------+
| id | name |
+----+----------+
| 1 | color |
| 2 | material |
| 3 | dept |
+----+----------+
product_classification
+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
| 1 | 1 | blue |
| 1 | 2 | latex |
| 1 | 3 | paint |
| 1 | 3 | home |
| 2 | 1 | red |
| 2 | 2 | latex |
| 2 | 3 | paint |
| 2 | 3 | home |
+------------+-------------------+-------+
因此，假设有人搜索 paint，您会执行以下操作:
So, say someones search for paint, you'd do something like:
SELECT p.* FROM product p WHERE name LIKE '%paint%';
这将返回 product 表中的两个条目.
This would return both entries from the product table.
执行搜索后，您可以使用如下查询获取结果的相关方面(过滤器):
Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:
SELECT c.id, c.name, pc.value FROM product p
LEFT JOIN product_classification pc ON pc.product_id = p.id
LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;
这会给你类似的东西:
+------+----------+-------+
| id | name | value |
+------+----------+-------+
| 1 | color | blue |
| 1 | color | red |
| 2 | material | latex |
| 3 | dept | home |
| 3 | dept | paint |
+------+----------+-------+
因此，在您的结果集中，您知道有些产品的颜色为 blue 和 red，它的唯一材料是 latex，并且它可以在部门 home 和 paint 中找到.
So, in your result set, you know that there are products whose color are blue and red, that the only material it's made from is latex, and that it can be found in departments home and paint.
一旦用户选择了一个方面，只需修改原始搜索查询:
Once a user select a facet, just modify the original search query:
SELECT p.* FROM product p
LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE
p.name LIKE '%paint%' AND (
(pc.classification_id = 1 AND pc.value = 'blue') OR
(pc.classification_id = 3 AND pc.value = 'home')
)
GROUP BY p.id
HAVING COUNT(p.id) = 2;
所以，这里用户搜索关键字paint，包括两个方面:facet blue 表示颜色，home 表示部门.这会给你:
So, here the user is searching for keyword paint, and includes two facets: facet blue for color, and home for department. This'll give you:
+----+------------+
| id | name |
+----+------------+
| 1 | blue paint |
+----+------------+
所以，总而言之.尽管它在 Solr 中是开箱即用的，但在 SQL 中可以相当容易地实现它.
So, in conclusion. Although it's available out-of-the-box in Solr, it's possible to implement it in SQL fairly easily.
这篇关于分面搜索 (solr) 与通过 PHP 进行的旧过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
