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
Search results for "secondary index"
Pessimistic Optimism: The Case Of Unexpected Deadlocks
Despite the fact I've been developing software in one way or another for almost 20 years, I'm constantly surprised how things work, and, once the frustration wears off, I'm intrigued when things break. Lucky for me, over the last few weeks, I've been surprised, frustrated, and intrigued by what, on ... read moreFun MySQL fact of the day: multi-range read optimisation
So far this week, we've looked at 2 of the 3 Nested-Loop Join algorithms MySQL can use to join tables. Before we look at the third one, we need to take a quick detour. ... read moreFun MySQL fact of the day: so many index reads
On Friday, we discussed the separate roles that MySQL and InnoDB have while querying data. Specifically, we looked at how InnoDB reads data for MySQL and how MySQL may or may not be tasked with additional filtering of the data. We also briefly discussed how MySQL may choose to ... read moreFun MySQL fact of the day: got it covered
Yesterday, we considered a data access pattern that relied on a secondary index to find a MySQL row that needed to be read at least 3 times: twice by InnoDB and once by MySQL. I also promised we'd find a way to get that number down to just one ... 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: 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: necessarily coarse locks
If, like me, you're curious why, in yesterday's example, InnoDB acquires a next-key lock before and a gap lock after the records it is updating with equality in a secondary index when usingREPEATABLE READ (what a mouthful!), then today's Fun MySQL fact is for you! I found myself ... 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