Favorite List recommendations Cache VS Sessions

[eluser]boltsabre[/eluser]
Hi,

I've never designed anything like this before, so I was hoping for some input on what you would recommend.

I want to put in a few different "Favorite" lists across a site that is still in development. Each list is independent of each other, so for the sake of this post, I'll just talk about "jobs" (where "jobs" is a position vacant created by users.)

I will be the storing each "Favorite List" in its own 2 column "join / intercepting" many->many relationship table (ie, job_id, user_id) where job_id is a foreign key of the the main "jobs" table, and likewise with user_id -> user_table.

The "Add/Remove to/from Favorites" will be both non-js and ajax powered. So basically a user can click on an star icon, it fires an ajax request which will either insert or delete a row in the join table. This functionality is restricted to logged in users to prevent spam bots/crawlers from creating many extra db hits, however, an overzealous logged in user could still manually create many db hits by clicking each "job" in the "list view" quickly or repeatedly clicking on the same star icon (any thoughts on if I should put some kind of mechanism here to restrict it firing for some seconds or something???).

So here's the thing I'm a little concerned about. When I load a list of Job Ads (paginated at 30 results per page), I need to know if the user has them in their favorites list so I can display the correct star icon. I don't want to have to make a db call to my join table every time the user loads a Jobs List or paginates to the next page. I imagine in the future, this could get quiet DB intensive when I have, potentially 1 million+ rows in the join table.

My thoughts were to get all the users favorites on the first page load and assign them to an arrray and either store it in my cache (I'm using Phil Sturgeons file based cache) or in the Session. And when a user adds/removes a favorite update this array. I have very limited knowledge on performance issues when it comes to cache vs session, so your thoughts would be appreciated!

Because there will multiple independent Favorite lists, I'll be isolating the code into a library for obvious reasons!

So... any thoughts on if I should be writing this into the file based cache, or into the session? Any other general thoughts about my proposed implementation of Favorites? Anything I've missed??? Any and all thoughts are more than welcome!!!

[eluser]Otemu[/eluser]
Hi,

Seems like quite a big project your working on, hopefully my input helps.

"(any thoughts on if I should put some kind of mechanism here to restrict it firing for some seconds or something?"
You could use a session to store when favorite clicked then disable depending on how fast or how long ago, make a database call to check time and date although I am sure you want to avoid that or another option is to use JavaScript which could disable or enable the favorite option.

I think I would be weary if am storing to much data within the session so I probably take the file based cache method. I assume this can cache your queries too.

First step I would look at is how much the queries can be optimized
"this could get quiet DB intensive when I have, potentially 1 million+ rows in the join table"
starting out you could see how intensive heavy your queries are, maybe there not as heavy as you believe and need to actually benchmark/profile your results and see how much of an impact your data will have. Maybe you can make your queries more efficient and fast.

I guess a lot of this will come down to your server and how much load it can handle, it really be difficult to judge, regardless of whatever method you take your still be running the same queries, try the file based cache method and if you think it running too slow maybe give sessions a try, maybe the difference be hardly noticeable.

Another option but depending on how sensitive the data is would be to save the data on the client side, for instance I did a project with Google maps and made a real heavy database call, to speed up the process this was cached as a json array on the client side.

Well that my input hope it helps some what

I did a little google search and there was a few topics on this am not sure if you seen them already but I have pasted them below:
http://stackoverflow.com/questions/11978...che-in-php
http://stackoverflow.com/questions/49657...-vs-static
http://stevenbenner.com/2010/12/caching-...rformance/

[eluser]boltsabre[/eluser]
Thanks again for the reply Otemu. Yeah, pretty big project, looking to get it up onto a test server in the next 4 weeks for testing and then a beta launch.

>>”(any thoughts on if I should put some kind of mechanism here to restrict it firing for some seconds or something?”
You could use a session to store when favorite clicked then disable depending on how fast or how long ago, make a database call to check time an...
Yeah, not an option, the whole purpose of that question was to reduce db hits, so storing their last hit in a db and then running a query to get that time defeats the whole purpose of what I was trying to achieve :-)

DB tables and queries can't be any more optimised, its a 2 column table (fav_id, user_id) and both columns have indexes. The only queries are INSERT one row, DELETE one row, all by user_id, and SELECT all by user_id... pretty simple in that regard.

Guess you're correct, until I have some live data to play with, I wont really know what option is best. New host and this'll be my first site with them, so who knows. From all my research they look good, dedicated my_sql server, etc. Anyway, it's a completely new site, so I doubt I'll see any high levels of traffic in the first year until I get some brand awareness and search engine ranking love, so that'll give me plenty of time to monitor how things go.

I have gone with the session based approach, and the code is now finished, I'm not going to touch it again now, too many other tasks to get finished in the few weeks, so I'll push it live and see how sessions go!

Thanks a ton again for your input, always appreciated!!!

[eluser]Otemu[/eluser]
Hi,

Actually just thought of a much better and simpler method to handle the bookmark issue if someone goes bookmark crazy, have a hidden input on the page and just populate the last added favorite time, when a user clicks favorite you can now check this time and decide whether to add favorite or not.

Sometimes it seems tables can't be more optimised and maybe this is the case with your structure, however awhile ago I was using one table for a two level navigation had something like parentid, childid, navname, etc. I had a function that loop though all the top level menus and return all the child's, wasn't heavy at all and at the time seemed sufficient enough. Then I discovered Nested Set model, with one query I could now build this tree with ease, I am not sure if nested set model will help you in this case but if you happen not to know about it great article here.

Good luck with your project