Even if you don’t learn Perl, the test cases in trunk/common/t/QueryParser.t might be useful to you.

Thank you; Roland is a good listener 🙂

well, thank baron 🙂

Here’s the slides:

http://assets.en.oreilly.com/1/event/36/EXPLAIN%20Demystified%20Presentation%201.pdf

(these are of the 2010 talk – not the one I attended but I see the info is still there)

“How can I guess the alias and match it against the query?”

I think I picked this up during Baron’s “Explain Demystified” talk from two years back at the MySQL user conference.

Assuming you can parse the SQL and succesfully match the ci and co aliases in the explain output to the query parse tree, you then know that `co` is a DERIVED table. Together with the value in the id column for that table, you get “derived2” (as in, CONCAT(LOWER(select_type), id)

Try it with another query like this:

explain extended select id from world.City ci, (select code from (select * from world.Country) co) s1;

You’ll notice that it actually works.

Of course, don’t mistake my explanation as approval – this is an ugly mess, at least for what you want to achieve 🙂

Thanks; I’ve just seen it. I think mk-query-digest already uses a very smart SQL parser.
If I happen to work on my perl skills, I’ll take a better look.

It still does not help in matching the query against the execution plan.

mysql> explain extended select id from world.City ci, (select code from world.Country co) s1;
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |  239 |   100.00 |                                |
|  1 | PRIMARY     | ci         | index | NULL          | PRIMARY | 4       | NULL | 4079 |   100.00 | Using index; Using join buffer |
|  2 | DERIVED     | co         | index | NULL          | PRIMARY | 3       | NULL |  239 |   100.00 | Using index                    |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------+

How can I guess the <derived2> alias and match it against the query? How about two derived tables?

You are right; I don’t have to go to that depth; but the job becomes very difficult. I suspect there are other examples.

I suspect you could even do it with a hand-crafted operator precedence parser.