MySQL fails to EXPLAIN
I’m not MySQL’s biggest fan, but this one really surprised me. I was putting together some update queries today while working on a Django project, and I’m guessing most other database engines don’t do things like this:
mysql> EXPLAIN UPDATE scanner_news sn SET stock_id = (
SELECT MIN(id) FROM scanner_stock WHERE ticker = sn.ticker GROUP BY ticker);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'UPDATE scanner_news sn SET stock_id =
(SELECT MIN(id) FROM scanner_stock WHERE t' at line 1
mysql> UPDATE scanner_news sn SET stock_id = (
SELECT MIN(id) FROM scanner_stock WHERE ticker = sn.ticker GROUP BY ticker);
Query OK, 40146 rows affected (2 min 25.20 sec)
Rows matched: 40146 Changed: 40146 Warnings: 0
The query still ran? WTF?
That helpful error message? Well, as it turns out, there are two open issues stating EXPLAIN
isn’t allowed just yet for UPDATE
or DELETE
queries. I’m glad the output indicated that.
See Also
- MySQL deadlocking on simple inserts - March 7, 2012
- Python cached property decorator - December 10, 2010
- Using Guppy to debug Django memory leaks - September 16, 2010
- Recent Archweb Improvements - August 27, 2010
- Archweb DB Schema - June 3, 2010