Set up large database in MySQL for analysis in R

I have a pretty good suggestion. For 80MB use SQLite. SQLite is a super public domain, lightweight, super fast file-based database that works (almost) just like a SQL database. http://www.sqlite.org/index.html

You don't have to worry about running any kind of server or permissions, your database handle is just a file.

Also, it stores all data as a string, so you don't even have to worry about storing the data as types (since all you need to do is emulate a single text table anyway).

Someone else mentioned sqldf: http://code.google.com/p/sqldf/

which does interact with SQLite: http://code.google.com/p/sqldf/#9._How_do_I_examine_the_layout_that_SQLite_uses_for_a_table?_whi

So your SQL create statement would be like this

create table tablename (
  id INT(11) INTEGER PRIMARY KEY,
  first_column_name TEXT,
  second_column_name TEXT,
  third_column_name TEXT 
);

Otherwise, neilfws' explanation is a pretty good one.

P.S. I'm also a little surprised that your script is choking on 80mb. It's not possible in R to just seek through the file in chunks without opening it all up in memory?

The sqldf package might give you an easier way to do what you need: http://code.google.com/p/sqldf/. Especially if you are the only person using the database.

Edit: Here is why I think it would be useful in this case (from the website):

With sqldf the user is freed from having to do the following, all of which are automatically done:

• database setup
• writing the create table statement which defines each table
• importing and exporting to and from the database
• coercing of the returned columns to the appropriate class in common cases

See also here: Quickly reading very large tables as dataframes in R