[Phpwm] Multiple MySQL insert techniques

[Alex Mace]

Hi Pete,

In the first instance I'd just test it and see what happens - no point  
in refactoring your code unless there is actually going to be a problem.

Is a user receiving the e-mail going to be counted automatically as  
having read the message, or are you meaning that all 6000 users will  
click a link and then view the message at once? I presume the former.

There's a few ways you could speed up the inserts. You could use  
prepared statements, or you could do a batch insert like this:

INSERT INTO messages ( userId, messageId ) VALUES ( 1, 1 ), ( 2, 1 ),  
( 3,1 ), etc, etc

You can insert a large amount of values like that with relatively few  
problems - just be careful of the max_packet_size in MySQL, which  
limits the size of a query you can send. You can always break the  
query up to stay under this size.

Another possible answer would be to insert them into a temporary table  
in memory and then do and INSERT INTO ... SELECT to bulk insert them  
into the messages table in a single operation. I think that should  
remove some overhead.

It will depend on your particular circumstances. Like I said, I'd just  
try it with what you have now. You might find the limit with your  
current setup is 50000, or it could be 500. If your current method is  
limited then try what I or others suggest and see what works best for  
you.

Hope that helps,

Alex

This usually allows you to insert a large amount of data much quicker.
On 25 Mar 2009, at 15:13, 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