I have a table with many rows but they are out of order. Im using the field "id" as the primary key. I also have a "date" field which is a datetime field.
How could i reindex the table so that the entries are id'd in chronological order according to the date field
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.
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.
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 ALTER TABLE t ORDER BY col; The allowed syntax of ORDER BY is as in SELECT statements.