Can anyone please explain "storing" vs "indexing" in databases?

Question

What is storing and what is indexing a field when it comes to searching? Specifically I am talking about MySQL or SOLR.

Is there any thorough article about this, I have made some searches without luck!

Thanks

Answer 1

Storing information in a database just means writing the information to a file.

Indexing a database involves looking at the data in a table and creating an 'index' which is then used to perform a more efficient lookup in the table when you want to retreive the stored data.

Answer 2

Storing data is just storing data somewhere so you can retrieve it later. Where indexing comes in is retrieving parts of the data efficiently. Wikipedia explains the idea quite well.

Answer 3

From Wikipedia:

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. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and excludes all the other details in the table), yielding the possibility to store indexes in memory for a table whose data is too large to store in memory.

Storing is just putting data in the tables.

Answer 4

storing is just that saving the data to the disk (or whatever) so that the database can retrieve it later on demand.

indexing means creating some separate data structure to optimize the location and retrieval of that data in a faster way than simply reading the entire database (or the entire table) and looking at each and everyt record until the database searching algorithm finds what you asked it for... Generally databases use what is called a Balanced-Tree indices, which is an extension of the concept of a Binary-Tree. Look up Binary Tree on google/wikipedia to get a more indepth understanding of how this works...

Answer 5

Data

L1. This
L2. Is
L3. My Data

And the index is

This -> L1
Is -> L2
My -> L3
Data -> L3

The data/index analogy holds for books as well.

Answer 6

Storing vs. indexing is a SOLR's concept.

In SOLR, a stored field cannot be searched for or sorted on. It can be retrieved as a result of the query that includes a search on an indexed field.

In MySQL, on contrary, you can search and sort on unindexed fields too: this will be just slower, but still possible (unlike SOLR)