Sometimes you may feel the need to re-order the fields in one of your tables – you shouldn’t really have to do this, but some people prefer a ‘neat’ schema. The most obvious way of doing this is to recreate the entire table, or drop the field and recreate it, but there is an easier way that doesn’t involve dropping fields or tables.

First of all, you must ask yourself “Why do I want to do this?” If you need the columns returned in a different order for SELECT statements, just name the fields in the right order – relying on field order in the table is just setting yourself up for a future FAIL. If you want a ‘pretty’ schema, is it actually worth it? Re-ordering fields has no real performance benefits. ¹

You can reorganise a table without deleting any data, but just to warn you the MySQL server will lock the table while it’s sorting things out – if you’ve got a lot of data in the table, this may take some time. Are you really sure you need to reorder the fields?

I’ll demonstrate the command syntax needed with a very small example table:

mysql> CREATE TABLE bob (a INT, b VARCHAR(3), c DATETIME); Query OK, 0 rows affected (0.04 sec)   mysql> DESCRIBE bob; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | b | varchar(3) | YES | | NULL | | | c | datetime | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)   mysql> ALTER TABLE bob MODIFY b VARCHAR(3) AFTER c; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0   mysql> DESCRIBE bob; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | c | datetime | YES | | NULL | | | b | varchar(3) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

You need to use ALTER TABLE … MODIFY to do the edit. You’re effectively redefining the column, so make sure your new definition is the same as the current one (SHOW CREATE TABLE is useful here) otherwise your existing data may change.

The MySQL server will create a temporary table with the new field order and copy all of the existing data into it. Once that’s done, it will replace the existing table with the temporary table and release all locks.

¹ It can have a marginal benefit if there are fields in the table that are larger than a single disk block size – in these cases, (at least) two blocks would need to be read from disk to get the entire row. Given that all controllers and disks perform read-ahead these days, the cost of this extra read is as close to zero as to make no difference. Unless your filesystem is severely fragmented, to the point where these two data blocks are on different disk tracks, there really is absolutely no need to re-order fields – if it is that fragmented, you’ve got bigger problems to sort out!