Sheeri K. Cabral wrote:
The MySQL manual for 6.0 at
http://dev.mysql.com/doc/refman/6.0/en/alter-table.html says that the
following are online operations:
*
Alterations that modify only table metadata and not table data
can be made immediately by altering the table's |.frm| file and
not touching table contents. The following changes are fast
alterations that can be made this way:
o
Renaming a column or index.
o
Changing the default value of a column.
o
Changing the definition of an |ENUM|
<http://dev.mysql.com/doc/refman/6.0/en/enum.html> or
|SET| <http://dev.mysql.com/doc/refman/6.0/en/set.html>
column by adding new enumeration or set members to the
/end/ of the list of valid member values.
In some cases, an operation such as changing a |VARCHAR(10)|
column to |VARCHAR(15)| may be immediate, but this depends on
the storage engine for the table. A change such as |VARCHAR(10)|
to a length greater than 255 is not immediate because data
values must be modified from using one byte to store the length
to using two bytes.
It also lists renaming as a fast operation.
During a recent MySQL User Group meeting where we were discussing the
online ALTER TABLE options (new in 5.1), the following question came up:
Why isn't dropping an index/primary key/unique key/foreign key an
online operation? Or rather, why is it slow? It should be easy to
drop an index, there's no updating needed other than "don't use the
index object any more".
(the answer may very well be "it is a very fast operation")...at any
rate, this should be changed in Drizzle if it isn't already...
On-line meta-data updates are implemented by engines. If an engine
can't do an operation on-line, the server does a full table rebuild.
In a nutshell, the API:
* Asks an engine where it can do a specific operation. The engine
receives a bit map of operation types and a before and after table
structure. If the engine understands the operation, it return yes.
* If yes, the server calls the engine a second time to actually do
the operation. The operands are the same.
* After the operation is complete, the server calls once more for
reasons unknown (at least by me).
The purpose of the bitmap is so the engine can know what to look for
when comparing the table structures. This avoids the problem of an
engine not knowing enough about a prospective operation to know whether
it can do it or not. When in doubt, an engine should always say no.
Falcon, for example, was originally designed to do all meta-data updates
on-line. That said, it still needs code to map server deltas into
Falcon DDL, and the code must scope out the changes and decide whether
it has an mapping implemented. Not difficult for Falcon, but most
operations are likely to be lethal for other engines, particularly those
that depend on fixed length records.
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
