Whats the most efficient way to do updates with Zend_Db and MySQL.

Question

I have a method which loops over an array of objects and commits an update statement per object (a total of 15 objects with 5 fields each). This may have to be performed hundreds or even thousands of times a second, depending on the level of traffic we get.

What is the best way (aside from hardware considerations) to ease the load this will cause? I'm wondering if using a transaction would be better? i.e. looping through the objects, building the update statements and then committing in one hit?

I'm looking for query optimisation for MYSQL 5 (PDO). Somebody else will be in charge of load balancing / architecture etc.

Thanks,

Answer 1

I think in your case you should build a system based on cache and update your db from this cache. The transaction wouldn't help so much.

So I think you have to build a middle ware. something between your php front end and the actual db. This middle ware could handle caching data for the php frontend and could distribute over time the database update.