Instant Access to 5 million row CSV for MySQL

mysqlI was investigating the CSV storage engine for mysql, having never used it. I found out, much to my relief, that it’s a relatively new thing — went standard with 5.1, but certainly not in the kits of luddites like me who have to stay four or five versions back because we can’t afford downtime caused by unexpected glitches with new releases.

The CSV engine is as I expected: It allows MySQL to directly access CSV files. There are probably boocoo performance issues and I don’t know how easy it is to later convert a table with its engine declared as CSV to something more standard such as MyISAM.

I think the big plus of the CSV engine is that you can quickly and easily leverage your existing CSV files from excel, as long as you are not looking for excellence in relational, normalized database design. Having been out in the trenches, I would guesstimate that 98 percent of MySQL users would not know normalized form if it bit them in the butt and they still manage to get work done. The CSV engine would probably give you a speed advantage for quick and dirty projects but I don’t think it is a great idea for a long standing, constantly updated database.

This article even gives you precise instructions how to give MySQL direct access to an existing csv file without going through a lengthy import process.

In a nutshell, you issue a matching CREATE statement from inside MySQL, then you go to the filesystem item that mysql created for the data component and mv your existing file over the location and name of the empty csv that MySQL created to house the data.

This is out of band management: i.e. going beneath the management tools in the mysql clients. They don’t say what happens if you goof up and forget or mispell a field, and that is the caveat to any out of band mucking about outside a management system.

But still, it might be a useful idea to file away if you have a big CSV on the same or very fast access filesystem and waiting for an import would delay something critical.

1 comment to Instant Access to 5 million row CSV for MySQL

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.