Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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) –  ypercube Aug 15 '12 at 8:47
    
Dump it, it will faster thank import ... –  user10872 Aug 15 '12 at 11:16
2  
What type of queries will you be running against this table? –  ypercube Aug 15 '12 at 12:51
add comment

1 Answer 1

up vote 1 down vote accepted

MyISAM ROW FORMAT

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

ALTER TABLE mytable ROW_FORMAT=Fixed;

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:

INCREASED RAM

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

[mysqld]
key_buffer_size=4G

STEP 02) preload all MyISAM indexes

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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