0

Is the index re-built completely or is the index updated? If it is updated then what exactly is updated?

Assume InnoDB is being used.

Aseem Bansal
  • 6,722
  • 13
  • 46
  • 84
  • Indexes are designed for both quick search/sort and for minimal update overhead. You may be interested by [this link](http://www.informit.com/articles/article.aspx?p=377652) – Graffito Jul 08 '15 at 09:45
  • Just search for data structures b-trees,there are many implementation examples in C – Mihai Jul 08 '15 at 09:49
  • @Mihai Thanks for the suggestion but I am not looking to that level. Still thanks for being precise and offering the detail. – Aseem Bansal Jul 08 '15 at 09:52
  • @Graffito The link is good. Just wish the chapter "Loading Data Efficiently" was present. I is the performance degradation that I wanted to know about - what needs to be updated or re-created. – Aseem Bansal Jul 08 '15 at 09:59
  • The index implementations vary for different MySQL storage engines. But, in all cases, index are just updated when a row is inserted or updated. – Graffito Jul 08 '15 at 10:13
  • http://stackoverflow.com/questions/1108/how-does-database-indexing-work Credit where credit is due. Good answer to this already exists also a good read [offsite ](http://use-the-index-luke.com/sql/dml/insert) – xQbert Jul 08 '15 at 18:10

2 Answers2

1

All indexes for a table in MySQL are "immediately" updated (not rebuilt) as a row is INSERTed into that table. Ditto for DELETE. In some cases, UPDATE causes index update(s).

By "immediately", I mean that you cannot tell whether it is finished before control is returned to you, or whether there is some form of caching going on.

Most indexes in MySQL are BTrees. In a few cases, there is FULLTEXT, SPATIAL, or HASH.

Adding an entry to a BTree involves drilling down the "tree" (~3 levels for a million-row table) and adding a 'record' in the leaf node. This is fast enough that you cannot tell whether it is done live.

If you have a dozen indexes, then there are a dozen BTrees (or whatever) to update. This suggests you should not have more indexes than you need.

In InnoDB the PRIMARY KEY is "clustered". That is, the data and the PRIMARY KEY live together in a single BTree, ordered by the PRIMARY KEY and containing all the data.

In InnoDB, each 'record' in a secondary index (also structured as a BTree) contains a copy of the PRIMARY KEY. (This may be what Zafar is alluding to.)

A BTree index is very efficient for

  • "Point queries" -- Finding one row, given the 'key'.
  • "Range queries" -- Finding rows given a key range (eg, WHERE key BETWEEN 22 AND 44)
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The levels are fixed or would grow as number of row grows? – Aseem Bansal Jul 09 '15 at 08:05
  • Every _factor_ of 100 (or so) will necessitate another level. A _trillion_ rows will have a BTree of about 6 levels deep. The number of level is rarely important in performance; other things dominate. – Rick James Jul 10 '15 at 03:18
0

In short you can say that in innodb, every index is associated with clustured index (normally known as primary key) so whenever any index value updated then it (changed value) will again associated with clustered index.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30