This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine has---so if it seems like a duplicate, let me know how best to rephrase what I'm asking.

I have the following table: create table mytable (id1 int, id2 int, score float) engine MyISAM, with 50 billion records.

I plan to add an index on the first column (alter table mytable add index myindex (id1)), and I am wondering what MySQL parameters could be changed to take advantage of this extra memory (e.g. buffers?)?

Note: the engine type does not need to be MyISAM if that makes a difference.

  • This is a much better table than the one in your previous post. Did you already load your data? I assume you used mysqlimport or "load data", and loaded a file that is already sorted by the primary key, oh wait, you don't have a primary key, would id1 be a unique value? Then, execute typical queries, compare your "show variables" output with your "show status" output, and identify the bottlenecks.
    – Ursula
    Commented Aug 7, 2013 at 1:14
  • The other post was for a different use case (I ended up using HBase for that problem). I have not loaded the data into this new table yet, but will use "load data" unless there's a faster way. I was not planning to add a PK (since I won't do updates/deletes), and id1 would not be unique. Commented Aug 7, 2013 at 12:58

1 Answer 1


Your first concern is the data loading. Since you don't intend to have a primary key, but just an index, you should order your data before loading by the index column id1, in any UNIX flavor:

sort -n mytable.txt > mytable.sort

For loading the data, use either mysqlimport of LOAD DATA from the MySQL prompt. To speed up the loading, MYISAM tables benefit from increasing *bulk_insert_buffer_size*, *myisam_sort_buffer_size* or *key_buffer_size*, for INNODB tables, increase *innodb_buffer_pool_size* and *innodb_log_file_size*.

These increases might not be sufficient, and the loading still might slow down as the memory fills up. In that case, it is more efficient to load the data in chunks. You can monitor the speed by executing an hourly du in the mysql root directory:

while [ 1 ]
    du -hs database/.
    sleep 3600

Just for completeness (not relevant for this specific case): Disabling indexes also helps speedup the loading process.

Now to querying: This is a bit more complex, as it depends on your data, and the way you want to query your table. Biggest factors that influence performance are:

  • buffers: set by variables. Execute your typical queries and compare the "show variables" and "show status" output to identify bottlenecks.

  • indexes: you already covered that

  • joins: Depending on the way you join with other tables, it might be
    better to denormalize your table(s) in a data warehouse style,
    especially if you want to join two large tables.

Great articles on data loading and large table query optimization can be found at: http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/ http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

Review the default and allowed values for these and other variables at: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

  • I like your suggestion of sorting the data ahead of time. The first blog post your reference mentions the benefit of this on InnoDB engines, but I'm wondering, do you know if it is also equally beneficial to MyISAM? Given that this table will be read-only, do you see any advantage to InnoDB vs. MyISAM? Thanks Commented Aug 7, 2013 at 19:19
  • 1
    My tables are all in MyISAM (for historical reasons), and for my tables, sorting does make a big different for loading speed and performance, however, I haven't benchmarked it in years.
    – Ursula
    Commented Aug 7, 2013 at 20:24
  • 1
    For what it's worth to others, I presorted my data and removed indexes before loading it, then set myisam_sort_buffer_size=80G and myisam_max_sort_file_size=1000G before creating the index. Thanks for the tips Ursula Commented Aug 9, 2013 at 10:25
  • Final follow-up: index finished creating in 7.5 hours. I noticed that it created a temp file for the MYD file first (creating a 300gb file on disk, not using much ram), and then it was busy for next 5 hours (with 80gb memory in use), eventually writing a 280gb .MYI file (the index) to disk. In hindsight, I wonder if the .MYD file was created because I created the index after loading the data instead of 1) creating the index before loading the data, 2) disabling indexes, 3) loading the data, and 4) enabling indexes. Thoughts anyone? Commented Aug 9, 2013 at 17:43
  • @dolan - In addition to above, you can use specific tmpfs partitions using a chunk of memory that can be used for temp table operations that MyISAM may normally use the disk for, bypassing the disk bottleneck. Loads from a RAM based file dir are again significantly faster.
    – cerd
    Commented Aug 11, 2013 at 3:47

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.