保存搜索结果的数据库结构

时间:2022-09-10 19:21:47

I currently work for a social networking website.

我目前在社交网站工作。

My boss recently had the idea to show search results by random instead of normal results (registration date). The problem with that is simple and obvious: if you go from one page to another, it's going to show you different results each time as the list is randomized each time.

我的老板最近有想法通过随机而不是正常结果(注册日期)显示搜索结果。问题很简单明了:如果你从一个页面转到另一个页面,每次都会随机化列表,每次都会显示不同的结果。

I had the idea to store results in database+cookies something like this:

我有想法将结果存储在数据库+ cookie中,如下所示:

  • Cookie containing a serialized version of the $_POST request (needed if we want to do a re-sort)
  • Cookie包含$ _POST请求的序列化版本(如果我们想要重新排序,则需要)

  • A table which would serve as the base for the search id => searches (id,user_id, creation_date)
  • 一个表,它将作为搜索的基础id =>搜索(id,user_id,creation_date)

  • A table which would store the results and their order => searches_results (search_id, order, user_id)
  • 一个表,用于存储结果及其顺序=> searching_results(search_id,order,user_id)

Flow chart would look like something like that:

流程图看起来像这样:

  • After each searches I store the "where" into a cookie or session
  • 每次搜索后,我将“where”存储到cookie或会话中

  • Then I erase the previous search in "searches"
  • 然后我删除“搜索”中的上一个搜索

  • Then I delete previous results in "searches_results"
  • 然后我在“searching_results”中删除以前的结果

  • Then I insert a row into "searches" for the key
  • 然后我在“搜索”中插入一行来输入密钥

  • Then I insert each user row into "searches_results"
  • 然后我将每个用户行插入“searching_results”

  • And finally I redirect the user to somethink like ?search_id=[search_key]
  • 最后我将用户重定向到一些想法?search_id = [search_key]

There is a big flaw here : performances .... it is definetly possible to make the system OR down OR very slow.

这里有一个很大的缺陷:表演......它绝对有可能使系统或降低或非常慢。

Any idea what would be the best to structure this ?

知道最好的结构吗?

4 个解决方案

#1


What if instead of ordering randomly, you ordered by some function where the order is known and repeatable, just non-obvious? You could seed such a function with some data from the search query to make it be even less obvious that it repeats. This way, you can page back and forth through your results and always get what you expect. Music players use this sort of function for their shuffle feature (so that if you click back, you get the previous song, and if you click next again, you're back where you started). I'm sure you can divine some function to accomplish this... bitwise XORing ID values with some constant (from the query) and then sorting by the resulting number might be sufficient. I chose XOR arbitrarily because it's a trivially simple function that will get you repeatable and non-obvious results.

如果不是随机排序,而是通过某些功能订购,其中订单已知且可重复,只是不明显?您可以使用搜索查询中的一些数据来播种这样的函数,以使其重复更加明显。通过这种方式,您可以在结果中来回寻找并始终获得您期望的结果。音乐播放器使用这种功能来实现其随机播放功能(如果您单击后退,则会获得上一首歌曲,如果您再次单击,则会返回到您开始的位置)。我敢肯定你可以用一些函数来实现这个...按位XORing ID值和一些常量(来自查询)然后按结果数排序可能就足够了。我任意选择XOR,因为它是一个简单的简单函数,可以让你获得可重复和非显而易见的结果。

#2


Hum maybe, but doesn't the xor operator only say if it is an OR exclusive ? I mean, there is no mathematical operation here, as far as I know of tho.

嗯可能,但是xor运算符不会说它是否是OR独占?我的意思是,据我所知,这里没有数学运算。

#3


Sorry, I know this doesn't help, but I don't understand why your boss would want this?

对不起,我知道这没有帮助,但我不明白为什么你的老板想要这个?

I know that if I search for a person on a social network, then I want the results to be ordered by relevance and relevance only. I think that randomized results would just be frustrating for the user, but maybe that's just me.

我知道,如果我在社交网络上搜索某个人,那么我希望结果仅按相关性和相关性排序。我认为随机结果对用户来说只是令人沮丧,但也许这只是我。

For example, if I search for "John Smith", then first first batch of results better be people named "John Smith". Then show me similar names near the end of the results. I don't want to search for "John Smith" and get "Jon Smithers" as my second result.

例如,如果我搜索“John Smith”,那么第一批第一批结果最好是名为“John Smith”的人。然后在结果的末尾显示类似的名字。我不想搜索“John Smith”并将“Jon Smithers”作为我的第二个结果。

#4


Well, I'm with Matt in asking "Why?"

好吧,我跟马特问“为什么?”

I think rmeador has a good suggestion as well. You could randomly sort by a different field or some sort of algorithm. Just from the permutations of DESC / ASC on last updated or some other result field.

我认为rmeador也有一个很好的建议。您可以通过不同的字段或某种算法随机排序。仅仅来自上次更新或其他结果字段的DESC / ASC的排列。

Other option would be to do an initial search the first time and return only related ID's and then store the full ID's string in the database and each subsequent page is then a lookup against those ID's.

其他选项是第一次进行初始搜索并仅返回相关ID,然后将完整ID的字符串存储在数据库中,然后每个后续页面都会查找这些ID。

My two cents.

我的两分钱。

I can see a scenario where a randomized result set is useful but not for searching but for browsing profiles or artists or local events. It offers more exposure to those that wouldn't show up in a traditionally directed search.

我可以看到一个场景,其中随机化结果集非常有用,但不适用于搜索,但适用于浏览个人资料或艺术家或本地事件。它提供了更多接触传统定向搜索中不会出现的内容。

#1


What if instead of ordering randomly, you ordered by some function where the order is known and repeatable, just non-obvious? You could seed such a function with some data from the search query to make it be even less obvious that it repeats. This way, you can page back and forth through your results and always get what you expect. Music players use this sort of function for their shuffle feature (so that if you click back, you get the previous song, and if you click next again, you're back where you started). I'm sure you can divine some function to accomplish this... bitwise XORing ID values with some constant (from the query) and then sorting by the resulting number might be sufficient. I chose XOR arbitrarily because it's a trivially simple function that will get you repeatable and non-obvious results.

如果不是随机排序,而是通过某些功能订购,其中订单已知且可重复,只是不明显?您可以使用搜索查询中的一些数据来播种这样的函数,以使其重复更加明显。通过这种方式,您可以在结果中来回寻找并始终获得您期望的结果。音乐播放器使用这种功能来实现其随机播放功能(如果您单击后退,则会获得上一首歌曲,如果您再次单击,则会返回到您开始的位置)。我敢肯定你可以用一些函数来实现这个...按位XORing ID值和一些常量(来自查询)然后按结果数排序可能就足够了。我任意选择XOR,因为它是一个简单的简单函数,可以让你获得可重复和非显而易见的结果。

#2


Hum maybe, but doesn't the xor operator only say if it is an OR exclusive ? I mean, there is no mathematical operation here, as far as I know of tho.

嗯可能,但是xor运算符不会说它是否是OR独占?我的意思是,据我所知,这里没有数学运算。

#3


Sorry, I know this doesn't help, but I don't understand why your boss would want this?

对不起,我知道这没有帮助,但我不明白为什么你的老板想要这个?

I know that if I search for a person on a social network, then I want the results to be ordered by relevance and relevance only. I think that randomized results would just be frustrating for the user, but maybe that's just me.

我知道,如果我在社交网络上搜索某个人,那么我希望结果仅按相关性和相关性排序。我认为随机结果对用户来说只是令人沮丧,但也许这只是我。

For example, if I search for "John Smith", then first first batch of results better be people named "John Smith". Then show me similar names near the end of the results. I don't want to search for "John Smith" and get "Jon Smithers" as my second result.

例如,如果我搜索“John Smith”,那么第一批第一批结果最好是名为“John Smith”的人。然后在结果的末尾显示类似的名字。我不想搜索“John Smith”并将“Jon Smithers”作为我的第二个结果。

#4


Well, I'm with Matt in asking "Why?"

好吧,我跟马特问“为什么?”

I think rmeador has a good suggestion as well. You could randomly sort by a different field or some sort of algorithm. Just from the permutations of DESC / ASC on last updated or some other result field.

我认为rmeador也有一个很好的建议。您可以通过不同的字段或某种算法随机排序。仅仅来自上次更新或其他结果字段的DESC / ASC的排列。

Other option would be to do an initial search the first time and return only related ID's and then store the full ID's string in the database and each subsequent page is then a lookup against those ID's.

其他选项是第一次进行初始搜索并仅返回相关ID,然后将完整ID的字符串存储在数据库中,然后每个后续页面都会查找这些ID。

My two cents.

我的两分钱。

I can see a scenario where a randomized result set is useful but not for searching but for browsing profiles or artists or local events. It offers more exposure to those that wouldn't show up in a traditionally directed search.

我可以看到一个场景,其中随机化结果集非常有用,但不适用于搜索,但适用于浏览个人资料或艺术家或本地事件。它提供了更多接触传统定向搜索中不会出现的内容。