I was debugging the performance of a DELETE statement that contained a subquery in the FROM clause. As there is no EXPLAIN for DELETE, I converted it to a SELECT statement (and hoped the same optimizations were done). But I still had to wait for EXPLAIN to complete. EXPLAIN evaluates subqueries in the FROM clause for MySQL. This can make EXPLAIN take a long time and create load on a server. Recent versions of MySQL have had many improvements for subquery optimization, but the documentation for all versions states that this is still done. A feature request is open to change this. Feature requests are also open to get EXPLAIN for UPDATE, INSERT and DELETE.
Do other RDBMS products support EXPLAIN for subqueries in a FROM clause without evaluating the subquery?
Saturday, September 5, 2009
Subscribe to:
Post Comments (Atom)
Posts
Other RDBMS are doing better.
ReplyDeletePostgreSQL doesn't execute the query you're explaining (unless it's told so), it runs it up to the Planner and then prints the results.
ReplyDeleteSee http://www.postgresql.org/docs/current/interactive/using-explain.html for examples.
http://nopaste.narf.at/f4d1d8933 has a trivial example with DELETE and a subquery
It's obvious that you have to convert your write queries in to select queries for explain and what I recommend too, is to handle the subqueries induvidualy. Sure it's would be better to be supported, but since its not doing it, we have to deal with this.
ReplyDeleteI have some recommendation to help your self with things like some perl slow query log parser, which can convert your slow (write) queries in to selects to help you. (since my browser get mad, I can't insert links sorry:( )
The plan for SELECT is not always similar to the plan for DELETE as some of the optimizer code for SELECT is different. See http://bugs.mysql.com/bug.php?id=30584 for an example. So you also need to run the DELETE/INSERT/UPDATE command once for an InnoDB table and look at the transaction stats in SHOW INNODB STATUS to see how many locks it took, or use SHOW SESSION STATUS to figure out whether it used an index or did a full table scan. The full table scan is usually my biggest concern as that will lock all rows in the table.
ReplyDeleteWhat's really fun is when your subquery includes a stored procedure that does and UPDATE query. Presto: running explain can actually update the database.
ReplyDeleteThe work on not evaluating FROM subqueries for EXPALIN is the subject of the WL#3485. It's implemented but there are some bugs in it. Current source tree is available at launchpad.
ReplyDeleteRegards, Evgen.