Justin’s A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer’s site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with [...]
I’m further developing a general log hook, which can stream queries from the general log. A particular direction I’m taking is to filter queries by their type of actions. For example, the tool (oak-hook-general-log) can be instructed to only stream out those queries which involve creation of a temporary table; or those which cause for [...]
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. [...]
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 [...]
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 [...]
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 [...]
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 [...]