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 "performance"
Fun 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: group commit
We now know that each MySQL thread has its own binary log cache to which it writes the binary log events for a single transaction. We also briefly discussed that MySQL will, upon commit of a transaction, write the thread's binary log cache to the actual binary log file. And ... read moreFun MySQL fact of the day: change buffering
Yesterday, we considered the consequences of random-order inserts on aPRIMARY index. Unfortunately, this same problem exists in secondary indices, and usually to an even greater effect since the data are rarely ordered at insertion time. ... read more
Fun MySQL fact of the day: splitting pages
Last week, we looked at how InnoDB uses records, pages, and extents to organise data, and we mostly considered inserts on thePRIMARY index using a monotonic primary key. Before moving on to secondary indices, we'll consider random-order inserts. ... read more
Fun MySQL fact of the day: in defence of auto_increment
Monday's introduction to auto_increment maybe wasn't as glowing as it should have been, but we really shouldn't let one little auto_increment really is an invaluable tool, and today we'll consider why. ... read more
Fun MySQL fact of the day: the phantom record menace
Yesterday, we created an index to prevent a full table scan from locking the entirelist_of_things table during a single UPDATE. I mentioned it was an improvement to the problem, but not a complete fix. Today, we'll see why. ... read more
Fun MySQL fact of the day: tenant lock-out
A couple weeks ago, we very briefly touched on the subject about MySQL/InnoDB locking only single records. And since we're on the topic of multi-tenant schemas, I think it's a good time to focus on this subject. For now, we'll focus on using MySQL's default isolation,REPEATABLE READ. ... read more
Fun MySQL fact of the day: leveraging autocommit
By default, MySQL uses autocommit for all new connections, and, as noted yesterday, autocommit can be remarkably powerful when used carefully for two separate reasons: ... read more
Fun MySQL fact of the day: InnoDB read-only optimisation
TheI in ACID stands for Isolation, which is a property that determines whether or not a transaction can see the progress of other transactions. Transaction isolation is a complex system generally defined by the allowance of certain "read phenomena". ... read more