In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table's primary key are concatenated to the index. I.e. the primary key contents is part of every other index.
Read MoreMySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn't enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible. The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.
Read MoreHow important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.
Read More