0

I want to know if I fire query like insert into emp ... What are the steps involved in query ?

It will go through InnoDB buffer pool?

Thanks for your help.

closed as too broad by LowlyDBA, mustaccio, Max Vernon, Marco, McNets Jan 22 at 20:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • This question is way to broad to answer here.. if you really want to know it you could download and study the MySQL sourcecode.. – Raymond Nijland Jan 21 at 16:13
0

Here's a broad answer to your broad question.

All DMLs and SELECTs always goes through the buffer_pool in InnoDB. It is a cache of the blocks that compose the BTrees that compose the data (organized by the necessary PRIMARY KEY) and each secondary INDEX.

For DML (broadly):

  1. Fetch a block, if not already in the buffer pool.
  2. Modify whatever.
  3. Mark the block "dirty".
  4. Rinse and repeat.
  5. Eventually (as a background task), the block will be written back to disk. Meanwhile, it is in the cache so that other operations can avoid having to fetch it from disk.

Modifying a non-unique index involves the "Change buffer". That is sort of a "delayed write" for such changes.

SELECTs:

  1. Fetch a block (if not already in buffer pool).
  2. Fetch row(s) out of that block.
  3. Rinse and repeat.

Note: in both DML and SELECT, the discovery of where a row usually involves drilling down a BTree, which involves fetching nodes (blocks) of the BTree. They, too, are cached (and tend to live a long time in the buffer pool).

If you want more details, start another Question with more specifics.

Not the answer you're looking for? Browse other questions tagged or ask your own question.