MySQL EXPLAIN UPDATE

Question

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.

Solution 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?

Solution 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.

Comments

[dustin.schultz]

I don't have the option of moving to 5.6. What's the recommended way to figure out why a MySQL 5.5 update is slow if there's no explain in 5.5?

[Raymond Tau]

Explain plan works in 5.5, on SELECT statements. If you really want to see why update is slow, assuming it is the where clause in the update is slow, try to do an explain plan on a select statement with the where clause should give you some hint. Also, you may clone the db and upgrade to 5.6 for testing as well.

[Raymond Tau]

You may refer to this question's comment as well. dba.stackexchange.com/questions/49788/…