Re: Deleted table file behavior!

[Rick James]

You are approaching the logic 'incorrectly'. Think of the whole process this way...
1. The data & indexes must be persisted to disk (unless you are using MEMORY or BLACKHOLE engines).
2. Always reading/writing the disk immediately would make things slow. So...
3. How can RAM be used to "cache" things to cut down on I/O?
4. The integrity constraints of InnoDB (not MyISAM) add wrinkles (iblog, etc) to the processing and the ordering of events.

> I still need to know where this "hidden/default binary log" is stored on windows for MyISAM files. I Know that by default for innodb is on ib_logfile0 and ib_logfile1 but I dont know how it works on MyISAM

There is not necessarily any hidden place for things -- wouldn't that be a waste of effort?

MyISAM indexes are updated via read-modify-write through the key_buffer and the .MYI file. The read is skipped if the block is already in the key_buffer; the write is delayed until MySQL feels like writing it.

MyISAM data is updated via read-modify-write to the .MYD file, with the OS doing the caching.

InnoDB has both data and indexes in 16KB blocks. The blocks are aggressively cached in the buffer_pool. For ROLLBACK, UNDO, etc, the iblog files are also involved. InnoDB uses either ibdata1 or a .ibd file (depending on the setting of innodb_file_per_table at the time of the CREATE TABLE) for persisting the data and indexes.

Turning on the binlog is one step in setting up Replication or certain flavors of backup. It is optional; the binlog may never be written to. So, it is orthogonal to the .MYD, .ibd, etc files.