Search code examples
mysqloptimizationindexingsql-delete

Should I reset a table index / optimize after deletion of many rows?

I have a table with 1,000,000 records and I'm running a statement that's deleting ~700k rows. The auto-increment-index is of course still at 1,000,001. The highest primary key afterwards for example 40,000.

After such a huge deletion of rows should I manually set the index back to 40,001 or optimize the table in any way? Or doesn't MySQL care for this huge gap when inserting new rows and using the index in select statements afterwards (in terms of speed)?

Solution

  • The MySQL manual says:

    OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows

    But don't reset the primary key, it can mess things up. The INT datatype (presumably) has a lot of room to grow from 1M.

    In terms of query speed, it doesn't matter whether the index value is at 1 000 000 or at 1 000 000 000.