Let me explain the situation.
Suppose I create a new table with an auto_increment column, and I make 3 new inserts in it.
Table: id, name
Then I delete the last record and create a new one.
Now, mysql will remove the entry at record id “3”, but it will not add the new record at id “3”.
The new record will be added at record ID “4”.
So table will look like this:
I am thinking that if lots of deletes happen in a table, then lots of such empty spaces will get created in the database overtime, which would slow down the “select” queries eventually.
Is that correct?
How can I fix this issue?
I tried exporting the whole table and then rebuilding it.
But the export dump has the record ID field in it.
So even after exporting and importing, the table still looks like this:
I am not sure if the null record ID “3” is still present or not…
optimize table is not working either. It takes lot of time, and PHP time_limit expires or mysql goes away while doing that.