Busy — Fri, 11 Mar 2005 09:44:00 +0000

Hi mijator, Welcome to TWF.
This is a very old post, you should really start your own thread asking your question.

Quick answer: not defrag but you can write a script which takes all the info into an array, clears the database row then reinstall the info, if you have an auto increment index the numbers will start at 1 again.
There is no real need to do this however as the info, if sorted by a loop will display the results sequential anyway.

resetting autoindex with phpMyadmin

mijator — Thu, 10 Mar 2005 23:29:29 +0000

It is possible...had to do it recently...

It kept bugging me that when a row gets deleted the autoindex increments anyways (instead of filling the lowest number, it proceeds from where it last left off)...it's probably safer this way (as a way to ensure the index is unique and not being used anywhere else), but it ends up making datafiles hard to read because the index numbers jump around (i.e., are not in sequential order)...

At any rate, the way to get phpMyadmin to reset the autoindex to the lowest number available, go to the "Structure" page, then click on "Operations" and then click on "Defragment Table". You can check to see that it worked by going back to the Structure page and looking for the box/line that says "Next Autoindex" under "Row Statistic".

I'd like to be able to defragment each time I do a delete on a table, so that my index numbers remain sequential - does anyone know how to do that?

Thx,

Mij

Mark Hensler — Fri, 04 Apr 2003 20:02:49 +0000

There is no wildcard because you can't delete a single field from a recordset. That would be an UPDATE query setting the field to NULL.

mairving — Fri, 04 Apr 2003 15:26:42 +0000

You do need to be careful. If you have tables linked you need to make sure that you delete the data in the other tables or you data will get very mixed up.

Things to ponder:
Why is it:
SELECT * from tablename
and
DELETE from tablename
It seems that it should be:
DELETE * from tablename

I have typed that one in a few times.

nuk3 — Fri, 04 Apr 2003 13:35:00 +0000

Yep

ROB — Thu, 03 Apr 2003 09:51:18 +0000

oh, in that case simply "DELETE FROM tablename;" will empty the table and reset the counters

nuk3 — Thu, 03 Apr 2003 07:37:41 +0000

I did it on a table with no data in it. I just wanted to know how to do it so that if there are no records in the table and a user goes to add in info the auto increment value will be reset to 1. This could be the case after 13 entries (13 altogether) are deleted..

ROB — Tue, 01 Apr 2003 21:30:10 +0000

typically you dont want to reset the auto increment though as it provides a logical order to your records, unless you empty the table. emptying the table (DELETE FROM tablename) will reset the incrementer to zero.

resetting it manually with existing records, i assume, would be smart enough to skip existing id's, but dont count on your id's to have any meaning. (for example why would user id 3 who joined today come before user id 20 who joined 3 years ago?)

nuk3 — Sat, 22 Mar 2003 08:49:55 +0000

Thanks allot Mark

Mark Hensler — Sat, 22 Mar 2003 08:46:09 +0000

hehe.. maybe I should do more research before answering next time. Took only 30 seconds to find the answer via the search at mysql.com

"ALTER TABLE tbl_name AUTO_INCREMENT = 1;"

It was in the user comments, not in the official docs.