PHP MYSQL精炼搜索多个查询

I am currently building a site for a car dealership. I would like to allow the user to refine the search results similar to amazon or ebay. The ability to narrow down the results with a click would be great. The problem is the way I am doing this now there are many different queries that need to be done each with a COUNT total.

我目前正在为一个汽车经销商建立一个站点。我希望允许用户改进搜索结果，类似于amazon或ebay。通过点击缩小结果的能力将会非常棒。问题是我现在做这个的方式有很多不同的查询需要用一个总数来完成。

So the main ways to narrow down the results are:

所以缩小结果的主要方法是:

Vehicle Type
车辆类型
Year
一年
Make
使
Price Range
价格区间
New/Used
新/使用

Currently I am doing 5 queries every time this page is loaded to get the numbers of results while passing in the set values.

目前，每次加载此页面时，我都要执行5个查询，以便在传递设置值时获得结果的数量。

Query 1:

查询1:

SELECT vehicle_type, COUNT(*) AS total FROM inventory
[[ Already Selected Search Parameters]]
GROUP BY vehicle_type
ORDER BY vehicle_type ASC

Query 2:

查询2:

SELECT make, COUNT(*) AS total FROM inventory
[[ Already Selected Search Parameters]]
GROUP BY make
ORDER BY make ASC

Query 3,4,5...

查询3、4、5……

Is there any way to do this in one query? Is it faster?

在一个查询中有什么方法可以做到这一点吗?快吗?

3 个解决方案

#1

Your queries seem reasonable.

您的查询看起来合理。

You can do it in a single query using UNION ALL:

您可以在单个查询中使用UNION ALL:

SELECT 'vehicle_type' AS query_type, vehicle_type, COUNT(*) AS total
FROM inventory
...
GROUP BY vehicle_type

UNION ALL

SELECT 'make', make, COUNT(*) AS total FROM inventory ... GROUP BY make

UNION ALL

SELECT ... etc ...

The performance benefit of this will not be huge.

这样做的性能效益不会很大。

If you find that you are firing off these queries a lot and the results don't change often, you might want to consider caching the results. Consider using something like memcache.

如果您发现您正在频繁地执行这些查询，并且结果不会经常更改，您可能需要考虑缓存结果。考虑使用memcache之类的东西。

#2

There are a couple ways to rank data along the lines of data warehousing but what you are trying to accomplish in search terms is called facets. A real search engine (which would be used with the sites you mentioned) performs this.

有两种方法可以按照数据仓库的方式对数据进行排序，但是您试图在搜索术语中完成的任务称为facet。一个真正的搜索引擎(它将与你提到的网站一起使用)就可以实现这一点。

SEE: Faceted searching and categories in MySQL and Solr
参见:MySQL和Solr中的分面搜索和类别

Many sites use Lucene (Java-based) search engine with SOLR to accomplish what you are referring to. There is a newer solution called ElasticSearch that has a RESTful API and offers facets but you'd need to install Java, ES, and then could make calls to search engine that returns native JSON.

许多站点使用基于java的Lucene搜索引擎和SOLR来完成您所引用的内容。有一种新的解决方案叫做ElasticSearch，它具有RESTful API并提供facet，但是您需要安装Java, ES，然后可以调用返回本机JSON的搜索引擎。

SEE: http://www.elasticsearch.org/guide/reference/api/search/facets/
参见:http://www.elasticsearch.org/guide/reference/api/search/facets/

Doing it in MySQL without requiring so many joins might need additional tables and perhaps triggers and gets complex. If the car dealership isn't expecting Cars.com traffic (millions/day) then you may be trying to optimize something before it actually needs it. Your recursive query might be fast enough and you haven't reported that there is an actual issue or bottleneck.

在MySQL中这样做而不需要太多的连接，这可能需要额外的表和触发器，从而变得复杂。如果汽车经销商没有预期到Cars.com的流量(百万/天)，那么在它真正需要它之前，您可能正在尝试优化它。您的递归查询可能足够快，而且您还没有报告存在实际问题或瓶颈。

#3

Use JOIN syntax: http://dev.mysql.com/doc/refman/5.6/en/join.html

使用连接语法:http://dev.mysql.com/doc/refman/5.6/en/join.html

Or, I think you could write MySQL function for that. Where you will pass your search Parameters. http://dev.mysql.com/doc/refman/5.1/en/create-function.html

或者，我认为你可以写MySQL函数。在那里你将传递你的搜索参数。http://dev.mysql.com/doc/refman/5.1/en/create-function.html

To find where is faster you should do your own speed tests. That helped me to find out that some of my queries faster without joining them.

要找到哪里更快，你应该做你自己的速度测试。这帮助我发现我的一些问题在没有加入它们的情况下更快。

#1