Hi all,
I wonder if anyone can give me some advice before my site gets into trouble.
I have a list of users who have their own table, each user can add items to their account and add a tag to each item, so I have a user_items table which has their id, item, item_tag and one or two other things they need.
Now, the problem is, if at some stage I end up having lots of users and each user can have plenty of items - up to a few hundred at the most, this user_items table could get very, very large.
In order to use the site, I need to present an item cloud that displays each distinct item, and the size of the item in the display depends on the count of that distinct item. i.e. with a count of a hundred of item 2, it displays twice as large as item 3 which has say, only 50.
Now, each individual user can search the cloud and add items to their account. In order to do this, I use an ajax script that passes a request from clicking the item link on the display to the backend. The problem is that the page cannot be stateless, it must know which user is clicking on it so that it can add the item to that user's account.
What this means is that for each user, I have to search the entire user_items database so that I can build the interface with their individual session ids and user ids built into it. Now, I can cache each page for a few days for each user, but fundamentally, having to search a table with potentially millions of records for each user (which could also be a huge number) sounds like a load too much to bear.
Can anyone give me some guidance on the best way to build clouds of this nature, or if anyone knows of a sneaky way to build the cloud without having to search the entire table each time that would be great!
Thanks, and sorry for the huge post..
