Speed: MySQL vs File Output

I have a php script that will execute for about 1 hour each time, and during it's runtime, it will need to store a steady stream of comments about what it's doing for me to view later. Basically, each comment includes a timestamp, and a short description, such as "2/25/2010 6:40:29 PM: Updated the price of item 255".

So which is faster, outputting that开发者_运维百科 to a .txt file, or inserting it into a MySQL database? Also, should I use the timestamp from PHP's date(), or should I create a time object in MySQL?

Second part of my question is that since the program is going to run for about an hour, should I connect to MySQL, insert data, and close the connection to the MySQL database each time I log a comment, or should I just connect once, insert data for the runtime of the program, and then close the connection when the program exits, about an hour after creating the initial connection?

Thank you in advance for all your advice.

It depends on your need for the data at the end of the day. Do you need to be able to do Audits on the data outside of scrolling through a file. If you don't need to browse the data or store it in perpetuity, then a flat file will be faster than MySQL, most likely, if you are just appending to the end of a file.

If you need the data to be more useful, you'll want to store it in mysql. I would suggest that you structure your table like:

id  int
timestamp datetime default now()
desc varchar

That way you don't have to actually create a timestamp in PHP and just let mysql do the work, then you'll e able to do more complex queries off of your table. But, another consideration you'll want to think about is the volume of the data going into this table, as that will also affect your final decision.

If you're simply logging information for viewing later, writing to file will be quicker. Writing to the database still has to write somewhere, and you get the added overhead of the database engine.

In my experience, it's much faster overall to write the .txt file than to use MySQL to write the log. See, if you write comments into the DB, then you have to write more code to get those comments out of the DB later, instead of just using cat or more or vi or similar to see the comments.

If you choose the DB route: It's perfectly OK to keep a connection open for your hour, but you have to be able to handle "server went away" in case you haven't written to the DB in a while.

-- pete