Latest Content

2019-04-12 11:06 UTC

Fun MySQL fact of the day: index condition pushdowns

It feels like we've been discussing Using 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

2019-04-11 08:00 UTC

Fun MySQL fact of the day: collation filtering

Yesterday, we considered some cases where MySQL may indicate Using where; Using index in the Extra field of a query plan. Today, we're going to look at one more case that existed up until MySQL 5.7, and while I haven't made a habit of digging up too many bugs ... read more

2019-04-10 08:00 UTC

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

2019-04-09 11:22 UTC

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

2019-04-08 22:06 UTC

Fun 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 more

2019-04-05 20:02 UTC

Fun MySQL fact of the day: secondary responsibilities

We've still got more ground to cover on secondary indices, but first, we're going to take a quick detour to understand a little bit more about how MySQL interacts with the storage engines. ... read more

2019-04-04 11:29 UTC

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

2019-04-03 11:48 UTC

Fun MySQL fact of the day: it's all secondary

In the last couple weeks ending with yesterday, we'd discussed secondary indices a few times, but we never really discussed why you may want to use them. So, over the next several days, we will do just that. ... read more

2019-04-02 10:12 UTC

Fun MySQL fact of the day: change buffering

Yesterday, we considered the consequences of random-order inserts on a PRIMARY 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

2019-04-01 12:13 UTC

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 the PRIMARY index using a monotonic primary key. Before moving on to secondary indices, we'll consider random-order inserts. ... read more

« Previous 3 4 5 6 7 8 9 Next »