how to reindex mysql table

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 ta开发者_Go百科ble so that the entries are id'd in chronological order according to the date field

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.

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.

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.

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.

You can use ALTER TABLE t ORDER BY col; The allowed syntax of ORDER BY is as in SELECT statements.

I had to do something similar. The best way to do it was the following (you can run it in one SQL Query if you want, but bare in mind that this is a slow and very resource consuming operation):

BE SURE TO MAKE A BACKUP OF YOUR TABLE, INCLUDING STRUCTURE AND DATA BEFORE STARTING THIS QUERY!

ALTER TABLE your_table ADD COLUMN temp_id INT UNSIGNED NOT NULL;
SET @a:=0;
UPDATE your_table SET temp_id=@a:=@a+1 ORDER BY `date` ASC;
ALTER TABLE your_table DROP id;
ALTER TABLE your_table CHANGE temp_id id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);
ALTER TABLE your_table CHANGE COLUMN id id INT(10) FIRST;

Just don't forget to change "your_table" with the name of your table, and the ORDER BY columns. Here I explain you what you're doing this way step by step:

1. First you add a new column named "temp_id" (make sure it's not a name you're using already);
2. Next you add a temp variable equal to 0 (or to whatever you want for your ID to start from);
3. Then you update your table, row by row by the set ORDER logic, setting a value for your new column "temp_id" equal to the variable you've set, then increment this variable by 1 (you can do something funky here, for example if you want your ID's to be always even, the you can set @a+2);
4. Next step you drop (remove) your old column ID;
5. Then you change the name of your temp_id column back to ID and it as a positive integer with auto increment which is the primary key of your table.
6. Because ID now is the newly added temp_id column, it's located at the end of your table structure. To move it again as first column, you run the last query, to make sure it's the first column.

If you are using something like phpmysql this could be achieved by:

• going to the table (left side list of db's and tables), then
• from the options in the upper bar select 'SQL'. Follow the advice by @Ryun, then go to 'Operations' (from the upper bar),
• look for 'TABLE OPTIONS', leave everything except 'AUTO_INCREMENT' unchanged,
• set the 'AUTO_INCREMENT' value to 1 and press go at the bottom of the form.

What will this do, in all?

• It will set the id columns in each from 1 to {count}.
• Then it will reset the index of the table so that your next inserted row will equal +1 the number of columns (and not +1 the old index).

@Wyzard made reference to just ordering the columns by date when you retrieve them from the table (and not re-indexing). Since, indeed, the Primary Key should be arbitrary (except to any foreign keys and perhaps the consuming platform (but that is another matter)).