Discussion on "High Performance MySQL [Ch.5: Indexing for High Performance]"

Sayed Alesawy · 2021-04-17T12:39:15.859Z

So the last article discussed chapter 4 of High Performance MySQL titled Optimizing Schema and Data Types. Today's article discusses chapter 5 titled Indexing for High Performance. In the last chapter we discussed schema design choices and optimizati...

Thank you for sharing. It had a significant impact on me, and I truly appreciate the effort and knowledge you put into it.

SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 days); There is a subtlety in the new query that using an always changing value like CURRENT_DATE although we don't need the entire date, just the days, will cause this query to miss the cache all the time. Is this argument still valid? as there is no query cache any more (by default at least)

In the clustered indexes section, in the second paragraph, you said that that clustered means that the "whole table" is stored in the index leaf nodes, while in the third paragraph you said that the leaf nodes in the clustered index contain the entire "row", so which of them is true? Or you intended to say in the third paragraph that the leaf nodes have the row itself not a pointer to its position in disk?

I don’t see how they are contradicting? The entire table is in the clustered index and it’s stored as one row per leaf. Both statements are correct. And yes, for an innodb engine, there are no disk pointers.

Did that answer your concern?

Match a leftmost prefix is quite not clear. Could you please explain it in a more clear way?

If we have a composite index like this idx(a,b,c). A leftmost prefix is a or ab or abc and so on. Any query pattern than can be covered by a leftmost prefix of the index can in fact utility this index. Is that more clear?

It's really incredible. I can't find words to express how much I've enjoyed it.

Search Hashnode

High Performance MySQL [Ch.5: Indexing for High Performance]

Responses5