sql - MySQL update query using indexed column

Question

I have a table table1 and the columns are like this:

id - int
field1 - varchar(10)
field2 - varchar(10)
totalMarks - int 

Also, I have created an index using the column field1.

create index myindex1 on table1 (field1);

I need to update the table entries, either using field1 or field2.

UPDATE table1 SET totalMarks = 1000 WHERE field1='somevalue';

or

UPDATE table1 SET totalMarks = 1000 WHERE field2='somevalue';

Which update query will have good performance? Since we have created an index using field1, will it have a good performance if we use field1 in the where clause?

Answer 1

A simple update statement with a where clause with an equality comparison on an indexed column should use the index for the where clause.

This will not always improve performance, but it usually will on larger tables. On very small tables where the data all fits on a single data page, the engine needs to load both the index and the data page into memory, which can actually be a wee bit slower than just looking for the row on a given page. This is an edge case.

I would recommend using the version with the indexed column.