INDEXING is way to optimize performance of a database by minimizing the number of disk accesses required when a query is processed.
An index or database index is data structure which is used to quickly locate and access the data in a database table.
Indexes are created using some database coloumns.
- The first coloumn is the search key that contains a copy of the primary key or
candidate key of the table .these values are stored in sorted order so that the corresponding data can be accessed quickly(note that the data may or may not be
stored in sorted order).
- The second coloumn is the Data Reference which contains a set of pointers holding the address of disc block where that particular key value can be found.
There are two kinds of indices:
- ORDERED INDICES : Indices are based on a sorted ordering of the values
- HASH INDICES: indices are based on the values being disturbed uniformly across a range of buckets.the bucket to which a value is assigned is determined by the function called a hash function.
There is no comparision between both techniques,it depends on the database application on which it is being applied.
- ACCESS TYPES: e.g value based search,range,accesses,etc.
- ACCESS TIME:time to find particular data element or set of elements.
- INSERTION TIME:time taken to find the appropriate space and insert a new data
- DELETION TIME:time taken to find an item and delete it as well as update the index structure.
- SPACE OVERHEAD:additional space required by the index.
4)non clustered indexing
CASES TO CREATE AN INDEX:
Most database software includes indexing technology that enables sublinear time lookup to improve performance,as linear search is insufficient for large databases.
Suppose a database contains N data items and one must be retrieved based on the value of one of the fields .Asimple implementation retrieves and examines each item according to the test.if there is only one matching item ,this can stop when it finds the single item,but if there are multiple matches,it must test everything.this means that the number of operations in the worst case is 0(N) or linear time.since databases may contain many objects and since lookup is a common operation,it is often desirable to improve performance.
POLICING THE DATABASE CONSTRAINTS
indexes are good to create if we take the case of database constraints .indexes are used to create police database constraints,such as UNIQUE,EXCLUSION,PRIMARY KEY and FORIEGN KEY.an index may be declared as UNIQUE,which create an implicit constraint on the underlying table.databases systems usually implicitly create an index on a set of coloumns declared PRIMARY KEY,and some are capable of using an already existing index to police this constraint.many database system requires that both referencing and referenced sets of coloumns in a FORIEGN KEY constraint are indexed ,thus improving performance of inserts,updates and deletes to the tables participating in the constraint.
CASES NOT TO CREATE AN INDEX:
- If the indexed coloumn is never searched on and the table is heavily updated you dont the benifit of performance that indeces are for.in contrary you might suffer performance hit.
- one circumstance under which an index is pretty much unconditionally bad is if there is another index which uses the same coloumns (in the same order) as a prefix:
CREATE INDEX ix_good ON some table(col1, col2, col3);
CREATE INDEX ix_bad ON some table(col1, col2);
the bad index is a waste of disk space and slows down modify operations to no benifit.
- if the index coloumn is not used for searches there is no point in defining it.
if the values in that coloumn keep changing very frequently it will be extra work for database server(for re-indexing)
if there are too many inserts and deletes from table it will be extra work for server.