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?

  • 1
    What was the query that took 6 seconds? SELECT * FROM table WHERE pk=? Can you provide the table's structure? (SHOW CREATE TABLE output) Commented Aug 15, 2012 at 8:47
  • 2
    What type of queries will you be running against this table? Commented Aug 15, 2012 at 12:51

1 Answer 1



If the table is a lot of VARCHAR fields, I would love to suggest the following

For every MyISAM table you load, do this beforehand


This will increase query performance up to 15-30% without changing anything else. The side effect of doing this is having bigger tables and bigger indexes.

I have earlier posts on this:


If you have many indexes, you may want to increase RAM on the DB Server and then attempt to preload all MyISAM index pages into the key buffer.

First compute how big a key buffer you would like (in GigaBytes)

SELECT CEILIING(SUM(index_length)/POWER(1024,3)) SuggestedKeyBufferSize
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT
IN ('information_schema','performance_schema','mysql');

If SuggestedKeyBufferSize can be set up for the DB Server, then do the following (example 4G)

STEP 01) Set this in /etc/my.cnf


STEP 02) preload all MyISAM indexes

See my post on how to do this : MySQL warm procedure

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.