how to reindex mysql table

the way i would do it is to create a new table with auto increment index and just select all your old table into it ordering by date. you can then remove your old table.

How about something like a simple query using a variable:

set @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.

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.