We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key (which is just an autoincremented int) took 6-7 seconds, though MyISAM sometimes was a little faster at 5-6 seconds.
We only need to write the data once, and I've been using mysqlimport. With that in mind, how can I improve the query speed?
...it's worth nothing that we have 2 gigs of RAM and everything is one table (and it has to stay that way due to the nature of the queries). Is that the best performance I can expect given the hardware? Or is there something else I should try, like compression? Or really, I need a lot more RAM?