Apache Friends Support Forum

Help, please! Suggestions please on how to clean this up. I have a test database I am using to develop a php site and my computer crashed. Now I notice 3 million rows in a table for one item with auto_increment id numbers that somehow was added, perhaps because of the crash, don't know for sure. How to get rid of them, they are cluttering my table and causing ridiculous # of pages to scroll through? Without deleting the table and reconstructing it, is there any way to eliminate these 3 thousand entries and reset the auto_increment #s back? If so, what would the MySql command look like for this?

I am using PHPMyAdmin to manage my database.
PHP5.04, Mysql 4.1.13, Apach 2.0 on Windows XP

Back up that database first, in case the following fails.

Then use the function to check the tables, in case the crash damaged it. If the table status is not "OK", repeat the check. If it's still not "OK", try the repair function.

To delete the excess rows:

DELETE FROM table_name WHERE column_name > value;

The auto-increment value for the table can be changed from the Operations tab for the table. Or you can use the query:

ALTER TABLE table_name AUTO_INCREMENT = value;

Thank you Dave, all is now good. I followed your clear and easy solution and the auto-increment values have now reset back to where they should be!

Great help, I have learned something new that will come in handy in the future.