Date

A comment to the last article:

"I had trouble implementing something like this using mysql-proxy + lua. Basically I wanted a way to force the developers, when using dev systems by console, every query they execute also follow up with an EXPLAIN. The exact mechanics of it I still need to work out, but having an explain run after testing a query I thought would be a great idea, or at least, if lua could detect a table scan and flag it as a warning or similar. One day I hope to give it another try." — Bradley Falzon

Just check the current mysql-shell branch on launchpad.net.

Here's what the script in the background does:

  • execute the query
  • get warnings, if there are any
  • execute SHOW STATUS
  • if this was a table-scan and a Com_select, run EXPLAIN EXTENDED on it

Just see:

$ mysql-client
(not connected)> .con:connect(":3306", "root", "secret")
root@0.0.0.0:3306 []> select host from mysql.user;
+---------------------------------+
|              host               |
+---------------------------------+
| 127.0.0.1                       |
| jan-kneschkes-macbook-pro.local |
| jan-kneschkes-macbook-pro.local |
| localhost                       |
| localhost                       |
+---------------------------------+
OK (warnings: 0, auto-commit: false)
+-----------------------+----------+
|          var          |  value   |
+-----------------------+----------+
| Bytes_received        |      105 |
| Bytes_sent            |     8038 |
| Com_select            |        2 |
| Com_show_status       |        1 |
| Com_show_warnings     |        1 |
| Handler_read_first    |        1 |
| Handler_read_next     |        5 |
| Key_read_requests     |        2 |
| Last_query_cost       | 3.082984 |
| Opened_files          |        4 |
| Questions             |        4 |
| Select_scan           |        1 |
| Table_locks_immediate |        1 |
+-----------------------+----------+
OK (warnings: 0, auto-commit: false)
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys |   key   | key_len | ref  | rows | filtered |    Extra    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | index |          NULL | PRIMARY | 228     | NULL |    5 |      100 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
OK (warnings: 1, auto-commit: false)
+-------+------+------------------------------------------------------------+
| Level | Code |                          Message                           |
+-------+------+------------------------------------------------------------+
| Note  | 1003 | select `mysql`.`user`.`Host` AS `host` from `mysql`.`user` |
+-------+------+------------------------------------------------------------+
OK (warnings: 0, auto-commit: false)
root@0.0.0.0:3306 []> .quit()

Comments

Enable javascript to load comments.