需要有关以最少的数据库查询提供类别/搜索结果过滤的建议
我正在一家网上商店工作，需要一些关于收集数据以显示价格、免费送货和涉及数据库计数的其他选项的过滤选项的最佳方法的建议。我的脚本是在 mySQL 上用 PHP 编写的。
如果您查看亚马逊的产品类别（即 http://www.amazon.com/gp/search/ref=sr_nr_scat_14219011_ln?rh=n%3A14219011%2Ck%3Awii&keywords=wii&ie=UTF8 ）你会看到左侧边栏上的过滤选项旁边是每个类别的结果数。例如，任天堂有 163 场比赛。
我不认为这会存储在表中，因为变量太多，您需要不断更新数据库，但为每个过滤组运行查询以调整每个页面加载 5-6 个查询似乎也是不切实际的。
在缓存中检索大型数据集，使用 PHP 过滤结果然后仅显示一些结果是否更符合逻辑，或者对可能数千个项目的查询比运行一些辅助计数查询更糟糕？还有其他解决方案吗？
I am working on a web store and need some advice for the best approach to take in regards to gathering data to show filtering options for price, free shipping and other options where counts of the database are involved. My script is written in PHP on mySQL.
If you look at Amazon's product categories (i.e. http://www.amazon.com/gp/search/ref=sr_nr_scat_14219011_ln?rh=n%3A14219011%2Ck%3Awii&keywords=wii&ie=UTF8 ) you will see that next to the filtering options on the left hand sidebar are the number of results per category. For example, there are 163 matches in Nintendo.
I don't imagine this is stored in a table as there are so many variables you would constantly be updating the database but it also seems impractical to run a query for each filtering group to the tune of 5-6 queries per pageload.
Would it be more logical to retrieve a large dataset in cache, use PHP to filter results down and then just display back a few results or is a query of potentially thousands of items worse than running a few secondary count queries? Is there another solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
其实很实用。如果您可以使用索引来访问小型数据集（数百个），则 SQL 计数会非常快。 （但请参阅下文，因为确实最好不要这样做。）
这样做最终会比使用 SQL 慢。一旦整个缓存不再适合内存，您最终将不得不将其存储在磁盘或类似的东西上。那时，您最终将以与在数据库中进行过滤全表扫描大致相同的方式浏览缓存数据。
是的。就是使用Memcache来避免一遍又一遍地重做查询：
http://php.net /manual/en/book.memcached.php
It actually is practical. SQL counts on small data sets (a few hundred) are very fast if you can reach them with indexes. (But see below, because it is indeed better to not do them.)
Doing so will eventually be slower than using SQL. As soon as your entire cache no longer fits in memory, you'll end up resorting to storing it on disk or something similar. And at that point, you'll end up going through your cached data in much the same way as if you were doing filtered full table scans in the database.
Yes. It is to use Memcache to avoid redoing the queries over and over:
http://php.net/manual/en/book.memcached.php