I'm working with a Rails app backed by a MySQL database. One of the more-frequently used models is backed by a table with over 200,000,000 rows (and growing). We've been aware for a while that the way it's set up isn't scalable.

The feature I'm working on at the moment requires me to add a couple new columns to that table, as well as a compound index. My coworker did a test-run on her local dev machine and it took about an hour per column (didn't even test adding the index yet).

We're considering taking a different approach, building a new table with the needed columns, dumping the data from the old table to the new one, then pointing the Rails model at the new table instead. Feels like a short-term solution for a large looming problem, but I'm curious if anyone has insight on how to navigate this problem.

We've also talked about archiving the table's old records. It's a table that gets written to and read from a lot, but only with recent records (the app probably never needs to do anything with records more than a month old).

  • First of all, use one alter statement to do all of adding two columns and the compound index. ALTER TABLE table1 ADD col1 INT, ADD col2 CHAR(2), ADD INDEX (col1, col5);. More about ALTER
  • Archiving old records is a good idea. If it is very rarely needed, then some slowness is affordable (I think).
  • Depending on your engine that you use, dumping the data and importing it to a new table maybe slow if you can't disable the keys. If you drop all indexes and recreate them, that is slow again. If you can afford some down time, any method is ok.
  • If the tables keeps growing, and you don't want to archive, you may consider "Sharding" the table, but that would require some extra efforts at development level
  • Last idea, which I don't strongly recommend unless you can't afford downtime, is to create a new table with a primary key and the new column that you want to add. Use this PK to join the original table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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