Re-index table in mysql

Question

I have a table in my database that I would like to re-index.

Because some rows where deleted, I now have :

id     name      phone
1      xxxx      xxxxx
4      xxxx      xxxxx
6      xxxx      xxxxx

And I would like:

id     name      phone
1      xxxx      xxxxx
2      xxxx      xxxxx
3      xxxx      xxxxx

id is already auto-incremented. The change of id for a row won't have any impact on the rest of the site, it's just a single and very simple customer list.

I've searched on internet and found some things with REPAIR TABLE but I'm not sure it's adapted to my case :/

Thanks for your help !

What database are you using? What column you want to re-index? What is the problem? — Edwin Lunando, Jul 11 '15 at 09:40
The problem is that I have some id that doesn't exist anymore because the line was deleted. I would prefer to have 1, 2, 3, 4, 5 instead of 1, 2, 6, 9, 10. I want to reset the indexes and re-create them. — Nicolas Roy, Jul 11 '15 at 10:56
Possible duplicate of [remove gaps in auto increment](http://stackoverflow.com/q/9754244/1427878). You should pay special attention to [this answer](http://stackoverflow.com/a/28507586/1427878), in particular the external page it links to in the first paragraph. — CBroe, Jul 11 '15 at 11:02
`AUTO_INCREMENT` is not intended for such use. If you want particular numbers, generate them yourself. Gaps do no hurt anything. — Rick James, Jul 11 '15 at 18:28

Michael Sims · Answer 1 · 2022-05-16T18:06:46.603

This is how I did it

I have a table called Events that has these fields where the id field is primary key and auto incremented:

id
EventType
DateTime
Weight
Comment

To 're-index' the table I did things in this order:

Created new table based on old tables structure
Copy records into new table WITHOUT the id field from old table, sorting by old tables id field
Drop old table
Rename new table to old table name

Like this:

CREATE TABLE NewEvents LIKE Events;
INSERT INTO NewEvents (EventType, DateTime, Weight, Comment) SELECT EventType, DateTime, Weight, Comment FROM Events ORDER BY id;
DROP TABLE Events;
RENAME TABLE NewEvents TO Events;

The second command that copies the records over to the new table is what re-creates the new index numbers. And because the records are sorted by the id field of the old table, it will ignore any missing id numbers and just keep auto-incrementing the new tables id field.

It worked for me anyways, but I highly recommend testing this from a complete copy of the entire schema then testing with your app to make sure everything works properly before doing this on a live / production schema.

I've never used TRIGGERS before, so I'm not sure if creating a table that is LIKE another table actually re-creates the triggers or not., nor am I sure if it re-creates any foreign key relationships. My gut says that these things will NOT be re-created, but in my case, this didn't matter. The point is, don't do this haphazardly - TEST TEST TEST on non-production data before doing this and BE THOROUGH!

Re-index table in mysql

1 Answers1