[MySQL] impact of string indexes on Update

After you add a prefix index to a field of varchar type, the query time based on the condition of this Sub-segment is greatly reduced. However, for update operations, the time consumed increases sharply, the main reason is that MySQL performs Index Update while updating data. The following is a simple experiment. (1) first, update all the records of a table field with a hundred million records:

for idx in range(1, count+1):    sql = "update tbl_name set platforms='"+datetime.now().strftime('%Y%m%d%H%M%S%f')+"' where  id="+str(idx)+";"    cursor.execute(sql)

Statistics take about 4 hours.

(2) then add an index to the sub-segment of a table with hundreds of millions of records:

sql = "alter table tbl_name add index platforms_index(platforms(8));"cursor.execute(sql)

Then execute the update statement above. The statistics took nearly nine hours. (3) it can be seen that string indexes have a great impact on the performance of update and other operations. Although the above small test does not have much practical significance, it can be extended. If you do not add an index for this field, you can directly store the field data to another small table B, then, add an ID field in the table to map to small table B, and then execute the update operation. What is the performance? Theoretically, the performance should be related to the record repetition. If the repetition rate is high, small table B will be very small (unique deduplication ); the Update time is the time used to query table B fields through the original table ing + the time used to update table B records, which is generally less than the time used to update records + the time used to update indexes. But it's just an estimation. How can we wait for the next verification.