1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Removing empty space from database

Discussion in 'Databases' started by JEET, Sep 9, 2021.

  1. #1
    Removing empty space from database

    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
    1, john
    2, Alex
    3, jeet

    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:
    SEMrush
    1, john
    2, Alex
    4, Sam

    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:

    1, john
    2, alex
    4, sam

    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.

    Any ideas?
    Thanks
     
    JEET, Sep 9, 2021 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    27,225
    Likes Received:
    4,181
    Best Answers:
    118
    Trophy Points:
    665
    #2
    The deleted rows don't exist magically, they're gone forever.

    However databases like MySQL do have an optimise function which gets rid of any traces of deleted rows, tidies up indexes etc.
     
    sarahk, Sep 9, 2021 IP
    JEET likes this.
  3. mmerlinn

    mmerlinn Prominent Member

    Messages:
    2,986
    Likes Received:
    714
    Best Answers:
    7
    Trophy Points:
    320
    #3
    I don't know mysql, but most databases have a method to PACK the database and remove records marked for deletion.
     
    mmerlinn, Sep 9, 2021 IP
    JEET likes this.