Cuando tu eliminas un registro el archivo de base de datos elimina logicamente el registro, por tanto lo lo ves mas, pero queda un residuo ocupado, la opcion que mencionas "optimizar" lo que hace es reorganizar la info para que ese residuo desaparezca.
Aca esta lo que dice la documentacion del MySQL, lo mismo que digo pero explicado a su manera.
Cita: OPTIMIZE TABLE debe ser usado si ha borrado una gran parte de una tabla o si ha hecho muchos cambios a una tabla con filas de longitud variable (tablas que tienen columnas VARCHAR, BLOB o TEXT). Los registros borrados son mantenidos en una lista enlazada y las subsecuentes operaciones INSERT reusan las posiciones de los registros anteriores. Puede utilizar OPTIMIZE TABLE para reclamar ese espacio inutilizado y para desgragmentar el archivo con los datos.
En definitiva, esto no hara que se renumeren los registros, pero optimizara espacio y tambien el consumo de CPU ya que aliviana los procesos.
Igualmente si es un requisito por X causa el que si o si tengas que tener los registros numerados de manera consecutiva puedes:
- crear una tabla temporal vacia con la misma estructura
- recorrer tu vieja tabla
- leer los registros de la vieja tabla
- insertarlos en la nueva tabla
- eliminar la vieja tabla
- renombrar la nueva como vieja
y esta como tiene registros nuevos recien creados (aunque sean a partir de la otra) estaran numerados 1 a 1.
En este caso debes tener cuidado si el Id de esta tabla no guarda referencia con otras tablas asociadas, en cuyo caso deberias reasignar la nueva Id.
