SOT SQL question

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 = 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?

TIA,   Jeffrey

Why don't you just use a counter_cache on Token?

ActiveRecord was great for prototyping the proof of concept. But it's too slow for production. I need to keep it in all SQL.

Thanks,   Jeffrey

Quoting Rein Henrichs <>:

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

Quoting Rein Henrichs <>: