I have an items tables, a tokens table, and a items_tokens join table. There
can be multiple joins for an items, tokens pair, i.e. a token appears multiple
times in an item. I would like to increment a column in the tokens for EACH
time it appears in in the join table. It seems that "UPDATE tokens JOIN
items_tokens ON items_tokens.token_id = tokens.id set occurrences =
occurrences + 1 WHERE items_tokens.item_id = 339;" (for item 339) would do the
trick but it only increments each occurrences at most once, i.e. duplicates
are removed before incrementing occurrences. Is there a way to do this in ONE
SQL statement?
LMAO. Good luck with that. And don't tell all the people running Rails
with AR in production. I'm sure they'll be devastated to hear that
their sites are too slow.
Sorry, I wasn't specific enough. It is too slow for one of my applications
for just one of the models w/ a join table. When a cronjob that is invoked
every five minutes occasionally runs for over 20 minutes with actual data, an
application is too slow and it is time to start profiling and benchmarking.
The results are interesting, to me at least.
Pure Active Record: 7.2sec
SQL w/ join table: 0.77sec
SQL w/out join table: 0.20sec
The application is an adaptive RSS reader. Every article has many words w/
statistics for each word (occurrences, click-thrus, up-votes, down-votes,
etc.). It is faster to regenerate the word list on the fly each time and
select on the word's string field than use a join table with select with joins
on the IDs.
As always, YMMV. So measure, then cut (code).
Jeffrey
