This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...
I'll be speaking at the Percona Live event, held in London, October 24, 25, 2011.
My session is called Programmatic Queries: things you can code with SQL. It's a short 30 minute talk, in which I present underlying knowledge of the programmatic nature of SQL queries within MySQL, and how to take advantage of such knowledge so as to build faster, shorter, and sometimes unexpected queries.
This is not about stored routine programming, a classic programmatic aspect of MySQL, but rather about expected order of execution: of row evaluation, of control flow statements, of table inference, of time issues.
I have far too many examples, some real-world problem solvers, and some less common in …[Read more]
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 a LOT of subqueries, temporary tables, and vast amounts of data, even if only to return with a couple of rows.
While we worked to solve this, a developer showed me his own trick. His trick is now impossible to implement, but there’s a hack around this.
Let’s use the world database to illustrate. Look at the following view definition:
CREATE ALGORITHM=TEMPTABLE VIEW …[Read more]
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 a filesort, or full index scan, etc.
This is done by evaluating of query execution plans on the fly. I suspect the MySQL query analyzer roughly does the same (as a small part of what it does).
It’s almost nothing one cannot do with sed/awk. However, I bumped into a couple of problems:
- The general log (and the mysql.general_log …
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 important, and monitoring utilities provide with this information. But what does that tell the end user? Not much.
The experienced DBA may gain a lot. The user would be more interested in completely other kind of information. In between, some information is relevant to both.
Say we were managing an on-line store. We want to monitor the health of the database. But the health of the database is inseparable from the health of the application. I mean, having little to no disk usage is fine, unless… something is wrong with the application, which leads to no new purchases.…[Read more]
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 her position among other rows. Rows with identical scores should receive the same rank (e.g. both contenders got the silver medal).
Consider the following table (download score.sql):
mysql> select * from score; +----------+--------------+-------+ | score_id | student_name | score | +----------+--------------+-------+ | 1 | Wallace | 95 | | 2 | Gromit | 97 | | 3 | Shaun | 85 | | 4 | McGraw | 92 | | 5 | Preston | 92 | …[Read more]
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 [...]
If you've ever wished you could see MySQL's EXPLAIN output formatted as a tree, now you can. MySQL Visual Explain transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand.