MySQL EXPLAIN UPDATE

问题:

I am trying to answer the following question as part of my college revision:

Create an index on at least one attribute of a table in the ‘employees’ database, where you use the MySQL ‘EXPLAIN’ tool to clearly show the benefit (in terms or retreival) and the negative (in terms of update) of the creation of the index in question.

For the first part I have created an index on the employees table and used the following query before and after the index to prove it's beneficial from a retrieval perspective:

EXPLAIN SELECT * FROM employees WHERE birth_date = '1953-09-02';

This index had the effect of reducing the accessed rows from 300,000 to just 63.

Now, i'm stumped on how to do the second part. I expected to be able to use the EXPLAIN command with UPDATE but it doesn't work for that.

The UPDATE query i'm trying to analyse is as follows:

UPDATE employee SET first_name = 'first_name' WHERE birth_date = '1953-09-02';

Is this a suitable query to answer the question and how do I go about analysing.

Many thanks in advance.

回答1:

EXPLAIN UPDATE exists in MySQL 5.6 but not MySQL 5.5 by comparing the document of MySQL. Did you try that on MySQL 5.6 server?

回答2:

The reference doc of Mysql 5.6:http://dev.mysql.com/doc/refman/5.6/en/explain.html

As of MySQL 5.6.3, permitted explainable statements for EXPLAIN are SELECT, DELETE, INSERT, REPLACE, and UPDATE. Before MySQL 5.6.3, SELECT is the only explainable statement.