mysql search and index performance

Imagine you have a blog post that users can subscrive to comments Is is faster to search on a table dedicated to manage subscriptions like: ID, Post, User

or is it faster to have a field in the use开发者_如何学Crs table with their subcritions: Subscriptions=|2|4|18|21|33|

or is it faster to have a field in the post table with the users that subscrive to that post: Users_subscrive=|1|2|4|6|9|

It's better to have a separate table Subscriptions, with ID, Post, User.

(assuming ID the autoincrement PK of that table, Post a FK to a table Posts, User a FK to a table Users)

Both from a logical point of view (a subscription is its own entity, thus its own table) as for database performance, a separate table is the way to go.

This is a process called normalization, it will allow you to make joins, and perform complex queries, like "Give me the last 5 users that have subscribed to this post" or "get all posts for this user which have changed since his last visit".

It also does not restrict you with a limit of maximum subscriptions (where a fixed field in the users table always has a length).

It will allow you later to easily extend your model. For example, different kinds of subscriptions: get notified by RSS, by mail, favorite posts...

Last but not least, since you are using MySQL, which is a relational database, this way of working (relations, get it?) is much faster in MySQL. It allows for indexes (quick search), foreign keys (users can't subscribe to a post that does not exist, if a post gets deleted, all subscriptions get deleted automatically) and much more.

It'll be faster to search actual table fields if they're properly indexed.

In fact it should be faster even if they're not indexed!