When does MySQL attempt to update an index for a column?

Question

I'm trying to determine what situations MySQL updates an index. Say I have the following table:

CREATE TABLE MyTable (
  ID INT NOT NULL AUTO_INCREMENT,
  MyIndexedColumn VARCHAR NOT NULL,
  MyNonIndexedColumn VARCHAR,
  PRIMARY KEY (ID),
  INDEX MyNewIndex(MyIndexedColumn)
)

Then I run the following SQL to insert a row:

INSERT INTO MyTable (MyIndexedColumn, MyNonIndexedColumn) 
VALUES ('MyTestValue', 'MyTestValue');

I understand that this query will add some sort of hash key to a B-Tree index in MySQL for the value 'MyTestValue'.

Now, if I run the following statement, will that force that B-Tree index to be updated, even if I haven't changed the value of the column?

UPDATE MyTable SET MyIndexedColumn = 'MyTestValue', 
MyNonIndexedColumn = 'A New Value' WHERE ID = 1;

Is MySQL smart enough to determine that? Or by just making that column part of the update statement, am I telling MySQL that possibly something has changed, and it should do the work to update the index?

Answer 1

If you run that query in the MySQL client, you'll see something like

Rows matches: 1, Rows Updated: 0

So MySQL definitely knows when a row has changed or not - I'd assume from there that they're smart enough not to update the index from there.

Answer 2

When you perform an UPDATE MySQL reports the number of rows matched and the number changed. Running your example query gives the output:

Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

I would be very surprised if MySQL didn't then use that information to determine whether to update the index.

Answer 3

Not only is MySQL smart enough to not update the index if the value hasn't changed, it is smart enough to not rewrite the column value with the same value.

Answer 4

I did some testing on this, with mysql 5.0.41, comparing updates against two identical innodb tables (7 cols, all integers), except that one table had 5 indexes (a couple of which were 2-column), and the other table had no indexes. (Each table had its primary key index, though.)

Here's what I ended up with (the table without indexes is A, the table with indexes is B):

10k updates of an indexed column with a new value:
A:  76.8 seconds
B: 126.7 seconds

10k updates of a non-indexed column with a new value:
A: 27.6 seconds
B: 22.0 seconds

10k updates of a random column with its same value:
A: 1.4 seconds
B: 1.2 seconds

10k updates of a random column with an incremented value:
A: 12.2 seconds
B: 50.0 seconds

10k updates of an indexed column=>same value, non-indexed column=>new value:
A:  7.0 seconds
B: 10.5 seconds

I'm assuming that part of the reason the same/incremented value ones are faster is because I had to look up the row before doing the update, so it'd be cached in some form in mysql.

This all pretty much plays out what the others are saying, but gives some perspective on how much things are affected by the indexes. However, in the specific case Jim asked about, it looks like it might be as much as 50% slower.