我要寻找检索记录的下一个和previous记录而不运行完整查询的最佳方式。我已经制定了全面实施的解决方案，并想知道是否有更好的方法来做到这一点在那里。

比方说，我们正在建设一个网站，一个虚构的菜贩。除了他的HTML页面，每个星期，他要公布他的网站上的特别优惠列表。他希望这些要约驻留在一个实际的数据库表，并且用户必须能够以三种方式的报价进行排序。

每个项目还必须有一个详细的页面越多，文字的报价和信息化previous和下一个按钮。在previous和下一个按钮需要指向所述邻接项的取决于排序，用户已选择为列表的

显然，下一个按钮西红柿，I类必须在第一个例子苹果，类1，梨，I类，在第二和第三无

在详细视图中的任务是以确定下一个和previous项目，而无需每次运行一个查询，列表作为唯一可用信息的排序顺序（比方说，我们获得通过GET参数？排序= offeroftheweek_price ，而忽视了安全问题）。

显然，简单地使下一个和previous元素作为参数的ID是，想到的第一个解决方案。毕竟，我们已经知道的ID在这一点上。但是，这是不是一个不错的选择 - 它会工作在这个简单的例子，但不是在我的许多现实世界中的用例

在我的CMS我目前的做法是使用的东西我有一个名为排序缓存。当列表被加载后，我存储在名为表中记录的项目位置 sortingcache 。

 名称（VARCHAR）项目（文本）

offeroftheweek_unsorted生菜;西红柿;苹果I;苹果II;梨
offeroftheweek_price西红柿，梨，苹果I;苹果II;生菜
offeroftheweek_class_asc苹果II;生菜，苹果，梨，西红柿
 

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

在详细信息页面，我现在访问相应的 sortingcache 记录，取项目列，爆炸了，搜索当前项目ID，并返回previous和一个邻居。

 阵列（当前=＆gt;中西红柿，
      下一个=＆GT; 梨，
      previous=＆GT;空值
      ）;
 

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

我的问题：

你认为这是一个很好的做法，找出相邻的记录变化的查询命令？

你知道在性能和简易性更好的做法？你知道的东西，使这个完全过时了吗？

在规划理论，有一个名字为这个问题？

的名称是排序缓存是适当的，可以理解的这种技术？

是否有解决这个问题的任何认同，共同的模式？什么是他们叫什么名字？

  

注意：我的问题不是如何构建列表，或者如何显示详细信息视图。这些只是例子。我的问题是在基本功能确定了创纪录的邻居，当重新查询是不可能的，并且到那里最快和最便宜的方法。

如果有任何不明白，请发表评论，我会澄清。

  

开始一个赏金 - 也许有这个外面一些更多的信息。

解决方案

下面是一个想法。你可以卸载昂贵的操作，以更新的时候杂货店插入/更新新的优惠，而当最终用户选择要查看的数据比。这似乎是一个非动态的方式来处理的分类数据，但它可能会增加速度。和，因为我们知道，总有一个折衷的性能和其它编码因素之间

创建一个表来保存下一个和previous，服务好，每个排序选项。 （另外，您也可以把该在报价表中，如果你将永远有三个排序选项 - 查询速度是一个很好的理由进行非规范化的数据库）

所以，你将有这些列：

在分类类型（未分类，价格，类别和价格DESC） 提供ID $ P $光伏ID 在一张ID

在详细信息中提供详细信息页面是从数据库中查询时，NextID和preVID将是结果的一部分。所以，你只需要一个查询的每一个细节上。

每次要约，插入，更新或删除，则需要运行一个过程，验证了sorttype表的完整性/正确性。

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.

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.

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.

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).

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.

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

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

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.

My questions:

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)

So you would have these columns:

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

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.

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.