1

I am trying to find out find out How DB index woks and when it should be used. I read some articles on that and one important one i found is at How does database indexing work?.

How it works:-

Advantage2:- After reading the discussion at above link , the one thing index helps is it reduces the number of data blocks to iterate through as explained in example1.

Advantage1:- But again one question came to my mind , after introducing the index also it has to search the index from index table(which any data store makes internally) which should be time again. So after further reading i found out that index are stored in efficient way usually using data structure like B trees thru which can drill down to to any value quickly and after going to node it will give us the exact memory location of record for that value given in where or join condition.Correct? So basically index srores the value of record on which we are creating index and memory location of actual record.

When it should be used:- AS we know if we create index on any column and if we insert/update/delete any value for that column , index needs to be updated for that column in index table. So it will take bit extra time and memory during CUD operation. So when it should be used .Imagine we create a customer one at a time from User screen.So total customer at end of day are 1 million. Now if we want to search customer for whose belongs to NewYork.here index will help a lot. Agreed it will slow down the insert customer a bit, it will be fractionally bad, but performance we will get during retrieval for new york customer will be exceptionally good.

Please correct me if you agree/disagree with above finding?

Community
  • 1
  • 1
M Sach
  • 33,416
  • 76
  • 221
  • 314

3 Answers3

1

Your general conclusions are pretty much ok.

Yes, for some queries, an index means less data blocks need to be read.

Yes, the default index type in Oracle is implemented internally using a B-Tree.

Yes, there is some overhead for Create/Update/Delete operations on a table with indexes - both in terms of performance and space used - but this overhead is usually negligible, and easily justified when the improvement to the performance of queries is considered.

I heartily recommend reading the Oracle Concepts Guide on indexes.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
1

Previous responds (and your conclusions) are correct. With regard to when to use indexes, it might be easier to discuss when not to use indexes. Here are a couple of scenarios in which it might not be appropriate to use an index.

  • A table in which you do a high-rate of inserts, but never or rarely select from it. An example of such a table might be some type of logging table.

  • A very small table whose rows all fit into one or a couple of blocks.

RichardJQ
  • 173
  • 3
1

Indexes speed up selects.
They do this by reducing the number of rows to check.

Example
I have a table with 1,000,000,000 rows.
id is a primary key.
gender can be either male or female
city can be one of 50 options.
street can be lots of different options.

When I'm looking for a unique value, using an index it will take 30 lookups on a fully balanced tree.
Without the index it will take 500,000,000 lookups on average.
However putting an index on gender is pointless, because it will not reduce the search time enough to justify the extra time needed to use the index, lookup the items and than get the data in the rows.

For city it is a border case. If I have 50 different cities a index is useful, if you have only 5 the index has low cardinality and will not get used.

Indexes slow down inserts and updates.

More stuff to consider
MySQL can only use one index per (sub) select per table.
If you want to use an index on:

SELECT * FROM table1 WHERE city = 'New York' AND Street = 'Hoboken'

You will have to declare a compound index:

ALTER TABLE table1 ADD INDEX index_name (city, street)
Johan
  • 74,508
  • 24
  • 191
  • 319