How long does it take to update mysql index

The establishment of MySQL index is very important for the efficient operation of MySQL. The index can greatly improve the retrieval speed of MySQL.

For example, if MySQL that is properly designed and uses indexes is a Lamborghini, then MySQL that is not designed and uses indexes is a human tricycle.

Indexes are divided into single column indexes and combined indexes. A single-column index means that an index only contains a single column. A table can have multiple single-column indexes, but this is not a combined index. Combined index, that is, an index contains multiple columns.

When creating an index, you need to ensure that the index is a condition applied to the SQL query statement (usually as a condition of the WHERE clause).

In fact, the index is also a table, which stores the primary key and index fields and points to the records of the entity table.

32 cores, more than 30 G of memory, and it took 7 minutes to create a non-clustered index for 10 million entries.

If the amount of data is 100 million, it is estimated to take more than 70 minutes.

Clustered index takes longer. This requires index sorting and building a branch index composite B-tree.

Generally, for massive data, it is better to create a new table, establish an index, and then import the data batch by batch.

A poor machine will basically crash or become a zombie when indexing 100 million data.

I can only wait slowly. If it doesn’t work for a day, I will use the method above.

Indexes are closely related to types. Generally, fixed-length fields are simpler than variable-length fields, consume less IO, and save time. The longer the compound index becomes, the more complicated it becomes. The second is one table with multiple indexes. In this case Various storage index structures will be derived, which will be even more time-consuming.

How many data pages, how many files, and how many slots each page has in the table will affect the time.

The above is the detailed content of How long does it take to update mysql index. For more information, please follow other related articles on the PHP Chinese website!