[Phpwm] Multiple MySQL insert techniques

[Dave Holmes]

Hi Pete,

Do this in one simple swift query using the select statement along the
following lines


insert into user_messages (user_id, message)
select user_id, 'my message blah blah blah'
from users;

remembering of course to quote the message string

This should be quick enough for you

Dave


<http://www.neteffekt.co.uk>
<http://threeboxes.co.uk>



pete graham wrote:
> Good afternoon developers,
>
> I would like your advice. I have a website which has an internal
> messaging system for users. The site admins have the ability to send
> out a message to all users on the site.
>
> The site has around 6,000 users currently and uses a MySQL Database.
> The admin messages go into a admin_messages table. They then get
> inserted into the messages table when a user logins into the site (the
> separate messages table is required as we need to record is a user has
> read messages).
>
> We now want to send an email notification when the admin sends a
> message. I'm concerned if I trigger 6,000 mysql inserts when an admin
> sends a message it will flood the DB connection. Am I being paranoid
> or could this kill the site/DB?
>
> Here are the solutions I've been considering:
>  - set up a cron job to batch insert the data into the messages tables
>  - use some form of clever sub query to insert the multiple items
> (could this also risk killing the db?)
>  - leave the message insert logic as it is and modify the batch email
> sending to work from a join on admin_messages with the users table
>
> I'm open to other suggestions, if anybody has them. I predict the user
> base of the site could grow to around 20,000 users.
>
> Thanks,
> Pete
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
>
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/phpwm/attachments/20090325/938569da/attachment-0001.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dave_signature.gif
Type: image/gif
Size: 6682 bytes
Desc: not available
Url : http://mailman.lug.org.uk/pipermail/phpwm/attachments/20090325/938569da/attachment-0002.gif 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: actiontag1.gif
Type: image/gif
Size: 5087 bytes
Desc: not available
Url : http://mailman.lug.org.uk/pipermail/phpwm/attachments/20090325/938569da/attachment-0003.gif