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, runEXPLAIN EXTENDEDon 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