Working on mycheckpoint, I have the intention of adding custom monitoring. That is, letting the user define things to monitor. I have my own thoughts, I would be grateful to get more input!
What would the user want to monitor?
Monitoring for the number of SELECT statements per second, InnoDB locks, slave replication lag etc. is very [...]
The common way of solving the classic SQL problem of ranking, involves a self join. I wish to present a different solution, which only iterates the table once, and provides the same output.
The ranking problem
Given a table with names and scores (e.g. students exams scores), add rank for each row, such that the rank identifies [...]
Sometimes MySQL gets it wrong. It doesn’t use the right index.
It happens that MySQL generates a query plan which is really bad (EXPLAIN says it’s going to explore some 10,000,000 rows), when another plan (soon to show how was generated) says: “Sure, I can do that with 100 rows using a key”.
A true story
A customer [...]
In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented.
We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider [...]
A popping question is: “Can an execution plan change for different storage engines?”
The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine.
We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and [...]