Analysis of the influence of string index on update in MySQL

This article examines the effect of string indexing on update. To share for your reference, the details are as follows:

After adding a prefix index to a field of type varchar, the time of conditional query based on this subsegment basically decreases greatly. For update operations, however, the time taken increases dramatically, mainly because mysql performs index updates at the same time as the data is updated.

Here is a simple experiment.

(1) Firstly, update all the records in the table fields of a certain 100-million-level record:

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)

It takes about 4 hours to count.

(2) Then index is added to the table subsegment of a hundred million level record:

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

The same update statement is then executed, which takes nearly nine hours.

(3) It can be seen that string index has a great impact on the performance of update and other operations.

Although the above small experiment does not have much practical significance, it can be extended to 1. If the index is not added to this field, the data of this field is directly stored in another small table b, and then an id field is added to this table to map to the small table b, and then the OPERATION of update is performed. What about performance?

Theoretically, performance should be related to the repetition rate of the record. If the repetition rate is high, the small table b will be small (unique deweighting). The updating time is the time spent in querying table b field through the original table map + updating table b record, which should be less time overall than updating record + updating index.

But it's just an estimate, and if you're interested in the result, you can verify it.

For more information about MySQL, please refer to MySQL Transaction Operation Skills summary, MySQL Stored Procedure Skills Collection, MySQL Database Locking Skills Summary and MySQL Common Functions Summary.

I hope this article has been helpful to you with the MySQL database.