A message could be sent to anything between 10 and 200 users.. This wouldn't work?
I didn't say your approach would not WORK. I said it would not SCALE.
Here is why:
At 1000 messages on file and 20 users, each user has to read and parse 1000 message address strings to find their 50 messages.
That's 20,000 data record reads for them to get their answers.
At 10,000 messages on file and 200 users, it will take 2,000,000 data record reads for each user to find their messages.
At 100,000 messages on file and 2000 users, it will take 200,000,000 data record reads for each user to find their messages.
As the number of messages and users increases, the load on the system will also increase. The system will get slower and slower until it finally fails.
This is because you are seaching thru the address and cannot use any database indexes to speed things up.
If your database tables had one table for the message and another for each message recipient, indexed by user, each user would only have to look at the records in the message_recipient table (plus a few index records behind the scenes as index overhead).
At small numbers of users and messages, your approach would be minutely faster - but no human would notice. At large numbers of messages and users, this approach would give good performance much longer.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
Marked as answer by jbear123 on Nov 29, 2007 09:09 AM
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 03:10 PM|LINK
I didn't say your approach would not WORK. I said it would not SCALE.
Here is why:
At 1000 messages on file and 20 users, each user has to read and parse 1000 message address strings to find their 50 messages.
That's 20,000 data record reads for them to get their answers.
At 10,000 messages on file and 200 users, it will take 2,000,000 data record reads for each user to find their messages.
At 100,000 messages on file and 2000 users, it will take 200,000,000 data record reads for each user to find their messages.
As the number of messages and users increases, the load on the system will also increase. The system will get slower and slower until it finally fails.
This is because you are seaching thru the address and cannot use any database indexes to speed things up.
If your database tables had one table for the message and another for each message recipient, indexed by user, each user would only have to look at the records in the message_recipient table (plus a few index records behind the scenes as index overhead). At small numbers of users and messages, your approach would be minutely faster - but no human would notice. At large numbers of messages and users, this approach would give good performance much longer.