PrateekSaxena Posted March 19, 2007 Share Posted March 19, 2007 Hi,:)Let us say that there is a website where anyone can just enter there name and a message and that message will be displayed on the front page. Also if someone clicks on the name we can see the posts by that guy. There will be millions of people posting messages.So what should I do? Should I make on table for the person and message query it if I have to show a sinlge person's post, or should I have a separate table for everyone? Or something else? Please give your suggestions... Link to comment Share on other sites More sharing options...
Mr_CHISOL Posted March 19, 2007 Share Posted March 19, 2007 Hi!I would start with a table for the messages, then how I would proceed depends on if I allow any visitor to post, or only those who has a login..If any visitor is allowed to post, you only need one table with mess. and then store the name as a string. To see all posts by that author you just get the name and compares it as so SELECT * FROM messages WHERE name = '$name' One problem with is that different visitors can post using the same name.The other way, to only let "members" to post, requires a second table with the informat abou the users (name/username, password, email etc)Then you just use the users id to assosciate the post with the user: SELECT * FROM messages WHERE id=$id Something, like that. Very abstract, but still...Good Luck and Don't Panic! Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 19, 2007 Author Share Posted March 19, 2007 No, I know the query, I mean would it be efficient to do so, or the SQL server take days to turn out the results of the queries if it has millions of messages? Link to comment Share on other sites More sharing options...
Mr_CHISOL Posted March 19, 2007 Share Posted March 19, 2007 I know that you know the quires, they was anly a "bi-product" of what I was writing.The essential part of my post was the other text.I Can't see another way, and I don't think it's "unenfficiant". A use of a seperate table for each user would be a bad way.Eaven with a million posts there shouldn't be a problem (unless you're going to display all at once ;?) ), not when it comes to getting the messages, but perhaps when it comes to space.One thing to do is perhaps a "cleanup"; once a week remove all messages that are more than a month old. Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 19, 2007 Author Share Posted March 19, 2007 Ok I will use only one table....BTW: the clean up aint possible! Link to comment Share on other sites More sharing options...
Mr_CHISOL Posted March 19, 2007 Share Posted March 19, 2007 Ok, Why isn't it possible? Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 19, 2007 Author Share Posted March 19, 2007 Cause we wanna see archives thats why Link to comment Share on other sites More sharing options...
Mr_CHISOL Posted March 19, 2007 Share Posted March 19, 2007 :?)Ok, that's a good reason. ;?) Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 20, 2007 Author Share Posted March 20, 2007 hehehe...so on table only? Link to comment Share on other sites More sharing options...
Mr_CHISOL Posted March 20, 2007 Share Posted March 20, 2007 Yes.Can't see any reason to why you should use (or need...) more than one table for this (other than usermanagement, but you didn't need that, so).If you would have a table for every user you would end up with ALOT of tables (say 1 mil. messages > [guessing] 10 000 - 50 000 individuals, or say 100 000 posts > 500 - 10 000 individs) It would be a nightmare to manage/administrate...Good Luck and Don't Panic! Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 21, 2007 Author Share Posted March 21, 2007 Thanks Mr CHISOL and I not panicking Link to comment Share on other sites More sharing options...
jesh Posted March 21, 2007 Share Posted March 21, 2007 I think that as long as you put an index on whichever field(s) you are using in your WHERE clause, even with millions of records, the results will come back relatively quickly.You may also consider storing all of the records in one table and a sort of "rollup" in another table which only has the messages that were received in the last day (or week or whatever). Then, to pull the most recent messages, you'd only have to pull from thousands of records and you'd only need to pull from the millions of records when you want to display an archived message. Link to comment Share on other sites More sharing options...
PrateekSaxena Posted March 21, 2007 Author Share Posted March 21, 2007 Alright....well MySQL is pretty fast!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now