Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.1k views
in Technique[技术] by (71.8m points)

sql - MySQL update query using indexed column

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?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...