This project is archived and is in readonly mode.
[PATCH] Optimization for dropping multiple columns in MySQL
Reported by Jordan Brough | September 27th, 2009 @ 01:46 AM
Dropping multiple columns on a large table in MySQL (at least version 5.0) can be slow proportional to the number of columns being dropped. That can be a drag on a large production database:
...MySQL, for almost all ALTER TABLE commands, copies the entire table into a temporary table, then renames the new table and drops the old one. ... The only type of ALTER TABLE call that doesn’t copy the entire table is a table rename.
(from http://labs.animoto.com/2008/08/06/activerecord-column-transformati...)
MySQL offers an extension to SQL that allows multiple columns to be dropped at once, so at least you only have to go through that once:
You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement. For example, to drop multiple columns in a single statement, do this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
(from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html)
Here's a patch that overrides
MysqlAdapter#remove_column to issue multiple drops in
a single statement.
I'm assuming this doesn't need additional tests since it's an implementation detail optimization of a feature that's already covered by tests but I'm happy to write some if someone has an idea about what to write.
The patch was applied to Rails 3.0 but the same diff should apply to Rails 2.3 as well.
Comments and changes to this ticket
-
CancelProfileIsBroken September 27th, 2009 @ 11:25 AM
- Tag changed from “activerecord, optimization” to “activerecord, bugmash, optimization”
-
Elomar França September 27th, 2009 @ 05:53 PM
+1, verified.
I think that optimizations like this (tiny but significative) are always welcome. The patch applies on 2-3-stable and master.
-
sr.iniv.t September 27th, 2009 @ 06:35 PM
+1 verified.
The patch applies cleanly on 2-3-stable and master. But the patch lacks tests.
-
Jordan Brough September 28th, 2009 @ 02:48 PM
What would new tests look like? Verify that execute was only called once for multiple drops? Wouldn't that be getting a little bit too concerned with the 'how' instead of the 'what'? Functionality-wise it seems to be covered pretty well already but I'm happy to add some tests if there's some ideas on what tests to add.
-
Rizwan Reza February 12th, 2010 @ 12:46 PM
- Tag changed from “activerecord, bugmash, optimization” to “activerecord, optimization”
-
Santiago Pastorino February 9th, 2011 @ 12:31 AM
- State changed from “new” to “open”
This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.
Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.
-
Santiago Pastorino February 9th, 2011 @ 12:31 AM
- State changed from “open” to “stale”
-
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
Jordan Brough