Search result timestamp sort order is useless.

[Dunbaratu]

You type a search term "word". You get lots of threads as hits, ordered from newest to oldest.

Except it's not the age of the mention of "word" that's being sorted on. It's the age of whatever post happens to be the newest one in the thread regardless of whether that post has a thing to do with your search term.

In other words the search is sorted this way:

For each post that has a hit for "word".
  Get the thread containing the post.
    Get the timestamp of the newest post in that thread.
      Sort by that timestamp.

When what would actually make sense is this:

For each post that has a hit for "word", grouping distinctly by thread picking just the most recent post that has "word" within each thread.
  Get the timestamp of that one post containing "word" in that thread.
    Sort by that timestamp.

That should be doable in SQL language in a database query.

The point is that the person searching wants a "newer" hit to mean a newer hit of THAT word, not a thread with a very old hit for the word that happened months ago but people have been adding to the thread after that about other subjects.

Edited December 11, 2014 by Steven Mading

[*Aqua*]

I understand what you want to have but it will slowdown database searches a lot. Usually forum systems make excessive use of the database cache which is held in memory. The most common searches are stored there, for example: all threads of the first page of a sub forum, all posts of the most recent threads, etc. - in short all data which will be accessed several thousend times per day. This data is updated in a fixed intervall (usually a few seconds or minutes) to further reduce the amount of work.

If you change the way how the searches are done you can easily break this high performance system. It simply isn't optimized for the new kind of work.

Yes you can optimize it again but that can take a month(!) and needs someone who knows all the tricks. Is it worth that?

[Dunbaratu]

The expense of the query can be greatly reduced by just giving you a way to utterly skip the "find thread where" part of it.

A query in which the intended result is "find posts where..." rather than "find threads where..." would give me exactly what I want, and I think a lot of other people too. Don't bother doing any of the joins between posts and threads tables. Just search the posts themselves, and don't bother culling out the multiple posts mentioning a word in the same thread, and it would let you see a list of the POSTS containing the search term and when you click on them you get taken to the link to that post within its thread.