CODEWITHSUNDEEP
phpmysqlasp.netjson
Milan
Milan

Reputation: 166

How to speed up mysql to accommodate thousands of rows

im making a site which allow users to add posts and can make comments. in that case im going to use a database tables to collect posts and comments for them. i have two table, one for posts, and other for comments. like below...

 --------------------------------------
 Table for posts
 --------------------------------------
 post_id | user_id | contents | media | time


 --------------------------------------
 Table for comments
 --------------------------------------
 comment_id | user_id |post_id |contents | media |time

is this structure is correct to full fill my work ? and i have little suspect that will mySQL database speed enough to serve when batabase tables filled out with thousends of rows by the time. so...then will it take a long time to find comments for certain post_id ?

Pls help me in this matter. Thanks.

Upvotes: 0

Views: 136

Answers (2)

Sri
Sri

Reputation: 77

yes. your structure is seems to be ok. MySQL is faster and power full than you think.
but, you must learn to mange. add index to database where it needed.
Indexes allow the database application to find data fast; without reading the whole table. http://www.w3schools.com/sql/sql_create_index.asp

add a foreign key constraint from the comments table to the posts table. http://www.w3schools.com/sql/sql_foreignkey.asp

Upvotes: 1

user2849406
user2849406

Reputation: 195

Your structure should be ok, and depending on the server MySQL should have no problem server millions of rows.

However you should remember to add indexes on the fields you plan to select on. If you know you will be fetching comments by post_id you should add an index on this field.

If you are planning to use innoDB, you should also remember to add a foreign key constraint from the comments table to the posts table (this will actually give you the index automatically). http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

If you want to select based on multiple fields (like post_id and user_id) you should also add a combined index on these fields.

Upvotes: 0

Related Questions