I have a webapp development problem that I've developed one solution for, but am trying to find other ideas that might get around some performance issues I'm seeing.
- a user enters several keywords/tokens
- the application searches for matches to the tokens
- need one result for each token
- ie, if an entry has 3 tokens, i need the entry id 3 times
- rank the results
- assign X points for token match
- sort the entry ids based on points
- if point values are the same, use date to sort results
What I want to be able to do, but have not figured out, is to send 1 query that returns something akin to the results of an in(), but returns a duplicate entry id for each token matches for each entry id checked.
Is there a better way to do this than what I'm doing, of using multiple, individual queries running one query per token? If so, what's the easiest way to implement those?
I've already tokenized the entries, so, for example, "see spot run" has an entry id of 1, and three tokens, 'see', 'spot', 'run', and those are in a separate token table, with entry ids relevant to them so the table might look like this:
'see', 1 'spot', 1 'run', 1 'run', 2 'spot', 3
rmbarnes - this must be where those UNION ops I saw in db basics years back suddenly make sense; I'll defintiely give this a performance run to see how it compares in overall speed
Just remember to use UNION ALL not just UNION, otherwise I don't think you'll get multiple rows returned with the same id like you want. – rmbarnes Sep 6 '08 at 20:23