What exactly is 'indexing' in Core Data?

Question

As an answer to a question I asked yesterday (New Core Data entity identical to existing one: separate entity or other solution?), someone recommended I index an attribute.

After much searching on Google for what an 'index' is in SQLite/Core Data, I'm afraid I'm not closer to knowing exactly what it is or how it speeds up fetching based on an attribute. Keep in mind I know nothing about SQLite/databases in general other than a vague idea based on reading way, way, way, too much about Core Data the past few months.

Answer 1

Simplistically, indexing is a kind of presorting. If you have a numerical attribute index, the store maintains linked list in numerical order. If you have a text attribute, it maintains a linked list in alphabetical order. Depending on the algorithm, it can maintain other kinds of information about the attributes as well. It stores the data in the index attached to the persistent store file.

It makes fetches based on the indexed attribute go faster with the tradeoff of larger file size and slightly slower inserts.

Answer 2

All these answers are good, but overly technical.

An index is pretty much identical to the index you'd find in the back of a book. Thus if you wanted to find which page a certain word occurred at, you'd go through it alphabetically and thus quickly find the all the pages where that word occurred.

If you didn't have an index, then the user would have to resort to going thru EVERY single page word by word, which could take quite a while. Thus, the index is created pretty much in this way ONLY once, and not every time the user wants to search.

Answer 3

Wikipedia has a great explanation of a database index:

"A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space."