What slows down indexes in a MySQL table

Question

So, I looked at the index literature in MySQL, but the following still confuse me. It has been explained that an index can slow down a MySQL query in terms of computational time. It was explained that this is based on queries that work with a table with a specified index called say index_A . I understand that at some point MySQL should rewrite its index file for index_A as a result of UPDATE, INSERT, or DELETE queries that appear in the corresponding tables. I don’t understand how exactly this is happening. Should this happen after every UPDATE, INSERT or DELETE? (This does not seem to be the case). Or is it necessary to perform a certain number of these operations before issuing a rewrite? (In which case, how do I know how much?) Or does it depend on the engine used for this table? Basically, I want to better understand what might cause my indexes to slow down queries so that I can better avoid this in the production process. Please let me know if anything I asked is unclear and thank you for your time.

Edit: It seems that the answer lies in a better understanding of Rtivs and Betry. These are the structures used to manage indexes on different machines in MySQL. I will continue to consider these issues and, I hope, will publish a definite answer regarding my results in the near future.

Answer 1

Yes, it will do after each request. Therefore, for the bulk insertion of MyIsam we can disable the keys and do all the insertions, and then enable the key. Please read to find out more about which . (In any case, this is not available in innodb)

Answer 2

I feel that you are confused about what the "update index" means. Let me clarify that you have a large table and you get some simplified indicators:

• If you INSERT (or update or ...) a million rows per table, it will take a million units of effort.

• If you INSERT another line that takes only 1 unit of effort.

It takes another million units of work to "rebuild the index," but this only happens if you explicitly specify the DROP and reCREATE index, some cases of ALTER TABLE and several other obscure actions.