Database structure for saving search results

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.

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

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

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

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.

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.