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

Question

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 containing a serialized version of the $_POST request (needed if we want to do a re-sort)
• A table which would serve as the base for the search id => searches (id,user_id, creation_date)
• A table which would store the results and their order => searches_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
• Then I erase the previous search in "searches"
• Then I delete previous results in "searches_results"
• Then I insert a row into "searches" for the key
• Then I insert each user row into "searches_results"
• And finally I redirect the user to somethink like ?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 ?

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