Please read the whole question before marking it as duplicate.
I know that we have only one way to remove a specific text from a file in C i.e. by rewriting the whole file except the text that we want to delete.But this method will not be very efficient if we have a file with thousands or millions of lines of text. Now since MyISAM is a storage engine which has to be made efficient because it is to be used for millions of records and it is made in C,so how does it accomplishes this without rewriting the whole file again?i.e. I am asking the technique which the developers of MyISAM used to delete a specific text from file without rewriting it again.
Just as in DOS, things are not "deleted", but rather "marked as deleted" such that to all subsequent operations, the deleted matter seems to be gone.
MyISAM:
InnoDB:
PRIMARY KEY) contains the row to delete; mark it as deleted.ROLLBACK resurrects the row.In either engine, there will be only a few IOPs (BTree drilldowns, reads, writes, logging) to delete the row. The actual number of IOPs depends on caching -- due to combining this delete with other operations on the table.
MyISAM's data is a stream file; the code will "seek" + read or write of the one record.
MyISAM's indexes are BTrees and are cached in the "key_buffer" (1KB blocks); InnoDB's data and indexes are BTrees and are cached in the "buffer_pool" (16KB blocks). All operations are seek + read/write of one block.
InnoDB redo/undo logs are, I think, streamed.
InnoDB's "double write" buffer is a block that is redundantly written. This is an ACID protection against a "torn page" wherein a block gets half written during a power outage. The unit of operation on most disks is a 512-byte "sector"; the unit for MyISAM/InnoDB is several such.
In the long run
So, if a record is only marked as deleted, is the disk space ever recovered? I emphasize disk space over "memory" RAM because RAM is just used as a cache.
Well, it depends. If you are "churning" the data -- deleting and inserting -- then the space freed up by a DELETE becomes available for an INSERT. However, because of the way the records are laid out, an INSERT may or may not reuse the space recently freed by a DELETE. But, in the long run, inserts will fill in the 'holes' left by deletes. But...
BTrees inherently have a small problem. Each node is a fixed size block. After doing a few deletes, the fixed size has not shrunk. After doing too many inserts, the block is "split" into two blocks (of the same, fixed, size). Still, over time, a BTree will gravitate to about 69% full. That is, what started out as 69 full blocks will (after a lot of churn) reach a steady state of about 100 blocks while still containing the same number of records.
So, a table will grow, but never shrink. But the growth is limited to some factor times the actual data size. What about shrinking?...
In both MyISAM and InnoDB, there is do automatic way to "defragment" and give back the wasted space to the Operating System. However, there is an SQL statement to do such. But don't use it; it is not worth the effort. It creates a new table, copies all the data over, rebuilds the indexes and renames the table back to what you had. A lot of effort; almost never much benefit.
Another thing... If two 'adjacent' BTree blocks are less than half full, the blocks will be combined. (This frees up a block for reuse in the given table, but does not give it back to the OS.)
What do the "large companies" do? Answer: "Nothing." I used to work for such, so I can speak from experience. In 10,000 tables on 100 systems, I identified only 2 cases where defragmentation was worth doing. And only monthly. And MyISAM, not InnoDB. You should not be using MyISAM today.