From things i Google, sqlite does't support clustered indexes(see Four: Clustered Indexes), but things i does not understand are:
Which means that in case your index is consecutive INTEGER, the records are physically organized within the database in that INTEGERs order, 1 then 2 then 3.
- If some records are erased from the table which consists of consecutive int index, in which the records from the new insertion go? From things i know, the records int ID is only going to grow,therefore the records is going to be appended to tail, right? Will it means the erased places are wasted?
- Within the situation of no consecutive INTEGER index, may be the sqlite table a heap table? i.e. the record is going to be placed in which the free space is first found.
1) Right, records is going to be happened to tail. Yes, erased places might be wasted when the database engine cannot re-utilize it easily. The unused places is going to be erased whenever you compact the database using command VACUUM.
2) Yes this type of SQL table is really a heap table. But indexes (of any sort) are precisely designed to access data as though records were sorted. Indexes are sorted values associated with records. However the new records aren't always placed in which the free space is first found. They're placed in which the database engine fill nice to put them considering unused space and time for you to write the binary data (happening in queue is faster than placing in the centre).