Using Primary Key Parts In A Secondary Index Of An InnoDB Table
Recently, I was asked what would happen if a primary key was explicitly put into a secondary index of an InnoDB table. For example, given a tableemployees, ... read more
Tagged with "innodb"
Fun MySQL fact of the day: hypothetical buffer pool size
Yesterday, we imagined a hypothetical 200GB database and an InnoDB buffer pool of 204.8GB. We also ignored some details to decide that we can hold something like 92% of the database in memory. Today, I propose we continue being ignorant of details and further explore this hypothetical situation. ... read moreFun MySQL fact of the day: buffer pool size
Yesterday, we started to discuss the InnoDB buffer pool and I noted that 80% of a system's memory makes a decent starting size for the buffer pool. However, this is somewhat meaningless since we didn't consider how much memory we'd want our system to have. Today we will. ... read moreFun MySQL fact of the day: the buffer pool
Since February, we've been intentionally working off a half-formed mental model about InnoDB. In just about all cases, we considered a model wherein InnoDB always reads from and writes to the on-disk index files to serve each query. And while considering a mental model like this can certainly simplify ... read moreFun MySQL fact of the day: everything is two-phase
Yesterday, we started thinking about InnoDB's redo log, and I left you with a thought to consider: how can MySQL keep multiple storage engines' redo logs in sync with the binary log. Today, we'll consider how MySQL does it. ... read moreFun MySQL fact of the day: MVCC-caused replication delay
Looking way back to March 11, we had discussed InnoDB's MVCC implementation. We also had discussed how InnoDB (by default) needs to keep around all versions of a record in the InnoDB undo log until all queries referencing the record either commit or roll-back. And while it may not ... read more
Fun MySQL fact of the day: a free ORDER
On February 28, we discussed that the order of results from a query in the absence of an ORDER BY clause are implementation specific. We also observed that in InnoDB, this implementation-specific behaviour tends to be the order of records as they are sorted in the index from which ... read more
Fun MySQL fact of the day: index condition pushdowns
It feels like we've been discussingUsing where quite a lot lately and you might be wondering, "there must be a better way". There is. In MySQL 5.6 and beyond, queries may leverage the Index Condition Pushdown Optimisation. ... read more