For a primary key of an integral type, why is it important to avoid gaps ?

Posted by Jacques René Mesrine on Stack Overflow See other posts from Stack Overflow or by Jacques René Mesrine
Published on 2010-03-29T02:27:46Z Indexed on 2010/03/29 2:33 UTC
Read the original article Hit count: 523

Filed under:
|

I am generating a surrogate key for a table & due to my hi/lo algorithm, everytime you reboot/restart the machine, gaps may appear.

T1: current hi = 10000000 
    (sequence being dished out .. 1 to 100)
       Assume that current sequence is 10000050

T2: restart system.

T3: System gives out the next_hi as 10000100 
    (sequence being dished out now ranges from 101 to 200)

T4: Next request for a key returns 100001001

From a primary key or indexing internals perspective, why is it important that there be no gaps in the sequences ? I'm asking this for a deeper understanding of mysql specifically.

Thanks

© Stack Overflow or respective owner

Related posts about mysql

Related posts about indexing