Menu
Is it any faster to add or drop multiple columns in one query, rather than executing a query for each column? For example, is this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
any faster than this?
ALTER TABLE t2 DROP COLUMN c;
ALTER TABLE t2 DROP COLUMN d;
489
We can alter multiple columns in a single query like this: ALTER TABLE `tblcommodityOHLC` CHANGE COLUMN `updated_on` `updated_on` DATETIME NULL DEFAULT NULL AFTER `updated_by`, CHANGE COLUMN `delivery_datetime` `delivery_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `delivery_status`;
Example 2: SQL query to change the datatype of multiple columns. We can change the datatype of multiple columns of a table. In our example, we want to change the column type of the first_name and last_name. The new datatype of the columns is varchar(200).
Now that ALTER TABLE handles multiple column changes as a single operation, there is no need to change the columns one by one.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
Yes, it should be faster to run a single ALTER TABLE statement than two.
In my experience (with InnoDB on 5.1 and 5.5), MySQL doesn't seem to modify the table "in place". MySQL actually creates a new table, as copy of the old table with the specified modifications.
Two separate statements would require MySQL to do that copy operation twice.
With a single statement, you give MySQL the opportunity to make all the changes with just one copy operation. (I don't know the details of the MySQL internals, but it's possible that MySQL actually does the copy two times.)
Other database engines (MyISAM et al.) may get processed differently.
I believe the InnoDB plugin and/or newer versions of InnoDB in the MySQL (>5.5) have some algorithms other than the "copy" method, at least for some changes, which allow for the table to still be available while the ALTER TABLE is running (for read queries). But I don't know all the details.
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
