simple multiple user selections/options

I see many implementations such as the Facebook like, forum karma, mark as read on forum posts and other simple options and selections available to multiple users on a given item.

I know I can implement this in mysql by creating a table which links say post IDs to liker user IDs for say, a like system.

My problem is, on a page with lots of posts, I will have to make a lookup for every post. I use prepared statements so that makes it faster for me.

Is there another way to implement these systems, if not, are there optimisations like database types or other tweaks that can make this faster?

Basically, is there a powerful, fast implementation of a many to many databa开发者_JS百科se interaction.

*EDIT*** I'm using opera mini and so I have issues with the ajax and js for commenting

Right now, I have a table with two columns. One for user id and the other for post id. Both are indexed and are used in foreign key constraints. I'm thinking of making a compound primary key across the two.

My main issue is for the karma. I allow users to vote on each post. The problem is, for each post, I need to get the total votes, determine if a user has voted to either allow the user to or not to vote.

My site allows many users to host their own sites and so I need to seriously optimize this.

Someone suggested I use memory tables for this.

NOTE** I can't use memcached.

I strongly suggest using something else than a MySQL db. I've written an opensocial app which had both heavy writes and reads to a database. It all started with a MySQL DB, I even switched to a dedicated master slave replication setup. But to no avail, it was expensive and it didn't scale very well.

The final solution was to use a NoSQL db which made the most out of RAM. My decision was mongoDB which has an activy community and solved my problem very well. MongoDB proofed to be highly scalable.

Still a little hazy about what you got so far, but I'll start it off and keep adding stuff if need be:

1. Make sure you're Indexing
2. Minimum lookups -so you get the list of posts that will pop up, use that list to match the like's, if they've viewed the article etc.
3. Using numbers - make sure all your comparisons are with numbers
4. If you're running queries, don't run a single query for each post
5. Is there a limit in your query? - make sure you use that
6. De-normalization is not a sin
7. You can partition your databases to decrease lookups (e.g. if data is older than 60 days and barely touched, move it to a secondary database/table, so the size of your table is not huge)

e.g. SELECT * FROM user_liked WHERE post_id IN (1,2,3) instead of

SELECT * FROM user_liked WHERE post_id = 1

Philipp Keller wrote a bunch of articles on tag systems based on MYSQL a few years ago. Just as Like-ing, Tagging is establishing a many-to-many relationship between a thing (tag, article being liked) and a user. The logic in his articles should be directly applicable to your problem as well.

Check out the comments as well.

• http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html Database Schemas for Tagging solutions
• http://www.pui.ch/phred/archives/2005/05/tags-with-mysql-fulltext.html Abusing the MySQL FULLTEXT indices for tagging and tag search (requires MyISAM, I'd not go there).
• http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html Performance Tests of tagging systems