[Phpwm] Multiple MySQL insert techniques

[Mike Tipping]

Use "insert delayed"

http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html


On 25/3/09 15:13, "pete graham" <petegraham1 at gmail.com> 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