As has been stated, the SQL insert statements shouldn't really be a worry as they can be handled with relatively few queries. The concern I'd have is the amount of emails you'd be throwing out - You'd probably want some kind of DB queue to store the messages ready to send and have a cron setup to periodically process a batch of them to stagger the server load<br>
<br><div class="gmail_quote">2009/3/25 Alex Mace <span dir="ltr"><<a href="mailto:alex@hollytree.co.uk">alex@hollytree.co.uk</a>></span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi Pete,<br>
<br>
In the first instance I'd just test it and see what happens - no point<br>
in refactoring your code unless there is actually going to be a problem.<br>
<br>
Is a user receiving the e-mail going to be counted automatically as<br>
having read the message, or are you meaning that all 6000 users will<br>
click a link and then view the message at once? I presume the former.<br>
<br>
There's a few ways you could speed up the inserts. You could use<br>
prepared statements, or you could do a batch insert like this:<br>
<br>
INSERT INTO messages ( userId, messageId ) VALUES ( 1, 1 ), ( 2, 1 ),<br>
( 3,1 ), etc, etc<br>
<br>
You can insert a large amount of values like that with relatively few<br>
problems - just be careful of the max_packet_size in MySQL, which<br>
limits the size of a query you can send. You can always break the<br>
query up to stay under this size.<br>
<br>
Another possible answer would be to insert them into a temporary table<br>
in memory and then do and INSERT INTO ... SELECT to bulk insert them<br>
into the messages table in a single operation. I think that should<br>
remove some overhead.<br>
<br>
It will depend on your particular circumstances. Like I said, I'd just<br>
try it with what you have now. You might find the limit with your<br>
current setup is 50000, or it could be 500. If your current method is<br>
limited then try what I or others suggest and see what works best for<br>
you.<br>
<br>
Hope that helps,<br>
<br>
Alex<br>
<br>
This usually allows you to insert a large amount of data much quicker.<br>
<div><div></div><div class="h5">On 25 Mar 2009, at 15:13, pete graham wrote:<br>
<br>
> Good afternoon developers,<br>
><br>
> I would like your advice. I have a website which has an internal<br>
> messaging system for users. The site admins have the ability to send<br>
> out a message to all users on the site.<br>
><br>
> The site has around 6,000 users currently and uses a MySQL Database.<br>
> The admin messages go into a admin_messages table. They then get<br>
> inserted into the messages table when a user logins into the site (the<br>
> separate messages table is required as we need to record is a user has<br>
> read messages).<br>
><br>
> We now want to send an email notification when the admin sends a<br>
> message. I'm concerned if I trigger 6,000 mysql inserts when an admin<br>
> sends a message it will flood the DB connection. Am I being paranoid<br>
> or could this kill the site/DB?<br>
><br>
> Here are the solutions I've been considering:<br>
> - set up a cron job to batch insert the data into the messages tables<br>
> - use some form of clever sub query to insert the multiple items<br>
> (could this also risk killing the db?)<br>
> - leave the message insert logic as it is and modify the batch email<br>
> sending to work from a join on admin_messages with the users table<br>
><br>
> I'm open to other suggestions, if anybody has them. I predict the user<br>
> base of the site could grow to around 20,000 users.<br>
><br>
> Thanks,<br>
> Pete<br>
><br>
> _______________________________________________<br>
> Phpwm mailing list<br>
> <a href="mailto:Phpwm@mailman.lug.org.uk">Phpwm@mailman.lug.org.uk</a><br>
> <a href="https://mailman.lug.org.uk/mailman/listinfo/phpwm" target="_blank">https://mailman.lug.org.uk/mailman/listinfo/phpwm</a><br>
<br>
<br>
_______________________________________________<br>
Phpwm mailing list<br>
<a href="mailto:Phpwm@mailman.lug.org.uk">Phpwm@mailman.lug.org.uk</a><br>
<a href="https://mailman.lug.org.uk/mailman/listinfo/phpwm" target="_blank">https://mailman.lug.org.uk/mailman/listinfo/phpwm</a><br>
</div></div></blockquote></div><br>