0

I understand that innoDB will store select statement result in the buffer pool, does InnoDB store newly inserted data (insert into statement) into buffer pool even after data has committed and wrote to disk?

2

Yes and no. Yes, buffer pool will contain the recently inserted data. No, the buffer pool doesn't contain SELECT statement output, that is what the query cache does, but it is generally harmful, deprecated and removes in MySQL 8.0. The buffer pool contains InnoDB pages (both index and data).

0
  • All data and index activity for all InnoDB read and write queries must have the data in the buffer_pool
  • The buffer_pool is structured around 16KB blocks, just as the BTrees in tables for data and secondary indexes.
  • Blocks are pulled into the buffer_pool when needed by queries.
  • Blocks are bumped out of the buffer_pool on (approximately) a least-recently-used bases.
  • How fast a block lives in the buffer_pool depends on how fast other blocks get bumped out of the buffer_pool.
  • Results of SELECTs never go into the buffer_pool. (Well, INSERT..SELECT.. is sort of an exception.)

From those 'facts' you can deduce the answer to your Question, and many more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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