English
| Showing entries 1 to 9 |
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
[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
[Read more...]
-0
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 (http://www.mysql.com/why-mysql/white-papers/mysql_wp_queryanalyzer.php) 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:
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!
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,
[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.
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):
[Read more...]mysql> select * from score; +----------+--------------+-------+ | score_id | student_name | score | +----------+--------------+-------+ | 1 | Wallace | 95 | | 2 | Gromit | 97 | |
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.
| Showing entries 1 to 9 |
