Can I combine multipe insert queries using a flat file?
The goal: I want to reduce load on the mysql database by combining multiple insert queries into one.
The question: Can I record insert information for multiple new records into a flat text file and load all the data into the database with a single INSERT query? Is this even a good idea?
The scenario: I have a high traffic PHP driven site receiving and recording individual access logs in a mysql database. Currently, each log is entered with its own insert statement in real time. The site writes one to two new entries into the database every second and bursts in traffic are maxing out the CPU.
The reasoning: I'm thinking that if I just write log data to a file and then parse it with a cron job every minute or so, I can reduce the number of INSERT statements and free up system resources.
Any thoughts on this idea or suggestions for other methods are very much appreciated.