优化下一个元素和上一个元素的查询 [英] Optimizing queries for the next and previous element

问题描述

我正在寻找最好的方法来检索记录的下一个和以前的记录，而不运行完整的查询。我有一个完全实施的解决方案，并想知道是否有更好的方法来做到这一点。

I am looking for the best way to retrieve the next and previous records of a record without running a full query. I have a fully implemented solution in place, and would like to know whether there are any better approaches to do this out there.

假设我们正在为一个虚构的蔬菜水果店建立一个网站。除了他的HTML页面，他每周都想在他的网站上发布特别优惠清单。他希望这些优惠居住在实际的数据库表中，用户必须能够以三种方式对优惠进行排序。

Let's say we are building a web site for a fictitious greengrocer. In addition to his HTML pages, every week, he wants to publish a list of special offers on his site. He wants those offers to reside in an actual database table, and users have to be able to sort the offers in three ways.

每个项目还必须有一个详细页面更多的是关于报价和上一个和下一个按钮的文本信息。根据用户为列表选择的排序，上一个和下一个按钮需要指向相邻条目。

Every item also has to have a detail page with more, textual information on the offer and "previous" and "next" buttons. The "previous" and "next" buttons need to point to the neighboring entries depending on the sorting the user had chosen for the list.

alt text http://www.pekkagaiser.com/stuff/Sort.gif？

显然，第一个例子中的西红柿，I类的下一个按钮必须是苹果，第1类，第二个是梨，I类，没有在第三个。

Obviously, the "next" button for "Tomatoes, Class I" has to be "Apples, class 1" in the first example, "Pears, class I" in the second, and none in the third.

细节视图中的任务是确定下一个和以前的项目，而不是每次运行查询，而排序列表的顺序是唯一可用的信息（假设我们通过GET参数？sort = offeroftheweek_price 获取，并忽略安全性影响）。

The task in the detail view is to determine the next and previous items without running a query every time, with the sort order of the list as the only available information (Let's say we get that through a GET parameter ?sort=offeroftheweek_price, and ignore the security implications).

显然，简单地将下一个和前一个元素的ID作为参数传递，这是第一个想到的解决方案。毕竟，我们已经知道了ID。但是，这不是一个选项 - 它可以在这个简化的例子中工作，但不是在我的很多现实世界的用例中。

Obviously, simply passing the IDs of the next and previous elements as a parameter is the first solution that comes to mind. After all, we already know the ID's at this point. But, this is not an option here - it would work in this simplified example, but not in many of my real world use cases.

我目前在CMS中的方法是使用我命名为排序缓存的东西。当加载列表时，我将项目位置存储在名为 sortedcache 的表中。

My current approach in my CMS is using something I have named "sorting cache". When a list is loaded, I store the item positions in records in a table named sortingcache.

name (VARCHAR)             items (TEXT)

offeroftheweek_unsorted    Lettuce; Tomatoes; Apples I; Apples II; Pears
offeroftheweek_price       Tomatoes;Pears;Apples I; Apples II; Lettuce
offeroftheweek_class_asc   Apples II;Lettuce;Apples;Pears;Tomatoes

显然，项目列真正用数字ID填充。

obviously, the items column is really populated with numeric IDs.

在详细页面中，我现在访问相应的 sortedcache 记录，获取项目列，爆炸，搜索当前项目ID，并返回上一个和下一个邻居。

In the detail page, I now access the appropriate sortingcache record, fetch the items column, explode it, search for the current item ID, and return the previous and next neighbour.

array("current"   => "Tomatoes",
      "next"      => "Pears",
      "previous"  => null
      );

这显然是昂贵的，仅适用于有限数量的记录，并创建冗余数据，但让我们假设在现实世界中，创建列表的查询是非常昂贵的（它是），在每个细节视图中运行它是不成问题的，并且需要一些缓存。

This is obviously expensive, works for a limited number of records only and creates redundant data, but let's assume that in the real world, the query to create the lists is very expensive (it is), running it in every detail view is out of the question, and some caching is needed.

我的问题：

• 你认为这是找出不同查询订单的邻近记录的好习惯吗？

• Do you think this is a good practice to find out the neighbouring records for varying query orders?

你是否知道在性能和简单性方面有更好的做法？你知道什么使这个完全过时吗？

Do you know better practices in terms of performance and simplicity? Do you know something that makes this completely obsolete?

在编程理论中，这个问题有没有名字？

In programming theory, is there a name for this problem?

名称排序缓存对于这种技术是适当和可理解的？

Is the name "Sorting cache" is appropriate and understandable for this technique?

是否有任何公认的常见模式来解决这个问题？他们叫什么？

Are there any recognized, common patterns to solve this problem? What are they called?

注意不是关于构建列表，还是如何显示详细视图。那些只是例子。我的问题是在重新查询是不可能的情况下确定记录的邻居的基本功能，以及达到最快和最便宜的方式。

Note: My question is not about building the list, or how to display the detail view. Those are just examples. My question is the basic functionality of determining the neighbors of a record when a re-query is impossible, and the fastest and cheapest way to get there.

如果有什么不清楚，请发表评论，我会澄清。

If something is unclear, please leave a comment and I will clarify.

开始赏金 - 也许这里有更多的信息。

Starting a bounty - maybe there is some more info on this out there.

推荐答案

这是一个想法。当杂货商插入/更新新的报价，而不是最终用户选择要查看的数据时，您可以将昂贵的操作卸载到更新。这可能看起来像处理排序数据的非动态方式，但可能会增加速度。而且，正如我们所知，性能与其他编码因素之间总是存在折衷。

Here is an idea. You could offload the expensive operations to an update when the grocer inserts/updates new offers rather than when the end user selects the data to view. This may seem like a non-dynamic way to handle the sort data, but it may increase speed. And, as we know, there is always a trade off between performance and other coding factors.

为每个报价和每个排序选项创建一个表来保存下一个和上一个。 （或者，如果您总是有三种排序选项，则可以将其存储在报价表中）查询速度是非正规化数据库的一个很好的理由）

Create a table to hold next and previous for each offer and each sort option. (Alternatively, you could store this in the offer table if you will always have three sort options -- query speed is a good reason to denormalize your database)

所以你会有这些列：

• 排序类型（未排序，价格，类和价格说明）


• 优惠ID


• 上一页ID


• 下一个ID

• Sort Type (Unsorted, Price, Class and Price Desc)
• Offer ID
• Prev ID
• Next ID

当从数据库查询提供详细信息页面的详细信息时，NextID和PrevID将成为结果的一部分。所以每个细节页面只需要一个查询。

When the detail information for the offer detail page is queried from the database, the NextID and PrevID would be part of the results. So you would only need one query for each detail page.

每次插入，更新或删除优惠时，都需要运行一个验证完整性/ sorttype表的精度。

Each time an offer is inserted, updated or deleted, you would need to run a process which validates the integrity/accuracy of the sorttype table.

这篇关于优化下一个元素和上一个元素的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！