1

Say I have a table with 3 columns: ID (PK), Name, Age.

Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update statement which looks like this:

UPDATE table SET AGE = 69 WHERE id = 'some_id';

I am only updating the AGE column, so will MySQL still update the indexes even though non of the indexed columns were modified or will it leave the indexes alone?

2 Answers 2

1

It is logical to think that the current databases are smart enough to not update the index which data have not changed.

But this logic should be straight supported by Mysql manual. And I've really found this statement on page which describes Speed of UPDATE Statements:

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed do not get updated.

3
0
UPDATE table SET AGE = 69 WHERE id = 'some_id';

Will be performed quite efficiently since id is the PRIMARY KEY.

Since AGE is not in any index, there is no INDEX will be modified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.