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 "sql"
Fun MySQL fact of the day: Extra confusing
If, over the last few days, you've started EXPLAINing your queries more, I commend you: you're on the path to becoming a database query optimiser extraordinaire. You may have also identified that the Extra column isn't a singleton value. You may have even seen something weird like Using where; ... read more
Fun 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 more
Fun MySQL fact of the day: OR not
We started discussing secondary indices yesterday, and we considered how we might create two separate indices on our user table. We also started to build an understanding of when MySQL can and shouldn't use an index for a query, but we omitted one fun fact. ... read more
Fun MySQL fact of the day: REPLACE your expectations
So, if we agree that we should avoid using INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE, surely we can use REPLACE INTO to upsert a row, right?! And yes, you can, if you want a DoS
Fun MySQL fact of the day: IGNORE at your own peril
Yesterday, we saw how INSERT ... ON DUPLICATE KEY UPDATE can unknowingly result in not-completely-expected situations. To get around that, you may have thought to use INSERT IGNORE, instead. Often, INSERT IGNORE is used when attempting to insert a row that may already exist. The underlying expectation a ... read more
Fun MySQL fact of the day: ON DUPLICATE KEY LEAK DATA
As developers, part of our job is ensuring system stability long after we are finished writing code. It is our responsibility to ensure the next developer in our place doesn't inherit a system littered with landmines and that our customers' data integrity and privacy is always a top priority. Today's ... read more
Fun MySQL fact of the day: a CHECKered past
A Check Constraint is a table constraint, described in SQL-89, that allows a database index to remain consistent based on a specified search criteria or expression. For example, we could ADD CONSTRAINT is_even_ck CHECK(my_int_col % 2 = 0) to ensure that the value of the my_int_col column in every row ... read more
Fun MySQL fact of the day: chaos without ORDER BY
ANSI SQL-92 states that the order of a result set in the absence of an ORDER BY is implementation specific. This means that the same query without an ORDER BY clause run n times may return rows in n different orders. ... read more