how to reindex mysql table

Why do you want the sequence of IDs to correlate with the dates? It sounds like you want to do ORDER BY id and have the rows come back in date order. If you want rows in date order, just use ORDER BY date instead. Values in an autoincrement ID column should be treated as arbitrary. Relying on your IDs being in date order is a bad idea.

You can use http://dev.mysql.com/doc/refman/5.1/en/alter-table.html t ORDER BY col; The allowed syntax of ORDER BY is as in SELECT statements.

The following SQL snippet should do what you want. ALTER TABLE test_table ADD COLUMN id2 int unsigned not null; SET @a:=0; UPDATE test_table SET id2=@a:=@a+1 ORDER BY `date`; ALTER TABLE test_table DROP id; ALTER TABLE test_table CHANGE id2 id int UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Keep in mind that you can never guarantee the order of an auto-incremented column once you start inserting and removing data, so you shouldn't be relying on any order except that which you specify using ORDER BY in your queries. This is an expensive operation that you are doing, as it requires indexes to be completely re-created, so I wouldn't suggest doing it often.

How about something like a simple query using a variable: @ROW = 0; UPDATE `tbl_example` SET `id` = @ROW := @ROW+1 ORDER BY `fld_date` ASC; This will order your rows like: 0,1,2,4,5...etc by your date.