SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Texas
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Thanks!

    Keeton

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Texas
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haven't gotten a response on this, so I came to my own conclusions in case they help someone else.

    First, I timed a loop of 1000 records as insert queries against 1000 of the same records into a log file using 'file_put_contents' with the FILE_APPEND option. Adding the records to a log file was MUCH faster (10 seconds or so for the queries, 1 second for the flat file).

    I'm combining the flat file with the MySql LOAD DATA INFILE (which is extremely fast) to add the stored data to the database. I'll run this as a cronjob which gives the added benefit of scalability as the load increases (I can run it every minute for now, and increase the time between crons if I need to).

    My biggest concern at this point is mal-formed data being run through the LOAD DATA INFILE function which causes it to crash. I'll have to add some error checking and alert email functionality in case something goes wrong.

    Any replies or comments on this would still be appreciated.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    When using LOAD DATA INFILE I use tab delimited text files, one record per line of course, not csv, so that a comma in the content doesn't result in the database seeing the wrong number of fields. Also be sure that you have a value present for every field.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •