I have modified the bboard system by adding a new table so that
users can mark certain threads as their own personal favorites. We
also have system favorites that everyone sees that I store as
user_id=0 in the same table. It seems that performance is getting a
bit worse, especially for display of system wide favorites that are
hit frequently. I am already using an index for user_id and msg_id in
the favorites table.
My question: Would it make any difference in performance if I
added separate table with only the user_id=0 system favorites rather
then leave them in the table with many rows?
A related issue is our bboard table that now has over 12,000
messages but most (guess 95%+) of the access is to the most recent
posts. (OpenACS appears to have about the same number of messages)
It seem that over time, large parts of the database almost never
get accessed.... Do recently accessed rows stay in memory? and
for how long? As a web application grows in tables and fields and
rows, is it just a matter of getting more memory to keep performance
acceptable?
Other perfomance and design considerations appreciated.
-Bob
