CSV vs MySQL performance

Question

Lets assume the same environments for PHP5 working with MySQL5 and CSV files. MySQL is on the same host as hosted scripts.

Will MySQL always be faster than retriving/searching/changing/adding/deleting records to CSV?

Or is there some amount of data below which PHP+CSV performance is better than using database server?

Answer 1

CSV won't let you create indexes for fast searching.

If you always need all data from a single table (like for application settings), CSV is faster, otherwise not.

I don't even consider SQL queries, transactions, data manipulation or concurrent access here, as CSV is certainly not for these things.

Answer 2

No, MySQL will probably be slower for inserting (appending to a CSV is very fast) and table-scan (non-index based) searches.

Updating or deleting from a CSV is nontrivial - I leave that as an exercise for the reader.

If you use a CSV, you need to be really careful to handle multiple threads / processes correctly, otherwise you'll get bad data or corrupt your file.

However, there are other advantages too. Care to work out how you do ALTER TABLE on a CSV?

Using a CSV is a very bad idea if you ever need UPDATEs, DELETEs, ALTER TABLE or to access the file from more than one process at once.