What slows down indexes on a MySQL table
So I have reviewed the literature on indexes in MySQL, but I am still confused by the following. It was explained that it is possible for an index to slow a MySQL query down in terms of computational time. It was explained that this is based on the queries that are operated on the table with said index, called say index_A. I understand that at some point MySQL must rewrite its index file for INDEX_A as a result of UPDATE, INSERT, or DELETE queries that occur on the relevant tables. What I do not understand is how exactly this happens. Must it happen after every UPDATE, INSERT, or DELETE? (This does not seem to be the case.) Or does it require a certain number of said operations to be executed before a rewrite is issued? (In which case how would I find out how many?) Or does this depend on the engine that is being used for that table as well? Basically, I want a better understanding of what could cause my indexes to slow queries down so that I can better avoid that in production. Please let me know if anything I have asked is unclear and thank you for your time.
Edit: It seems that the answer lies in a better understanding of Rtrees and Btrees. These are the structures used to manage indexes in various engines in MySQL. I will further review these and hopefully post a definite answer regarding my findings soon.
Yes, it will do after each query. So for MyIsam bulk insert, we can disable the keys and do all inserts followed by enabling the key back. Please read to know more on that. (This is not available in innodb anyways)
I sense that you are confused over what it means to "update the index". Let me elaborate by saying you have a big table and give you some simplified metrics:
If you INSERT (or update or ...) a million rows into a table, that takes a million units of effort.
When you INSERT one more row, that takes only 1 unit of effort.
To "rebuild the index" takes another million units of work, but this happens only if you explicitly DROP and reCREATE the index, some cases of ALTER TABLE, and a few other obscure actions.