I'm attempting to discover discover How DB index woks so when it ought to be used. I just read some articles on might one important one i discovered reaches How does database indexing work?.

How it operates:-

Advantage2:- After reading through the discussion at above link , the main one factor index helps could it be reduces the amount of data blocks to iterate through as described in example1.

Advantage1:- However one question found my thoughts , after presenting the index plus it needs to search the index from index table(which data store makes internally) that ought to the perfect again. So after further reading through i discovered that index are saved in efficient way usually using data structure like B trees through which could drill lower to to the value rapidly and after likely to node it'll provide us with the precise memory location of record for your value succumbed where or join condition.Correct? So essentially index srores the need for record which we're creating index and memory location of actual record.

When it ought to be used:- As you may know when we create index on any column and when we place/update/remove any value for your column , index must be up-to-date for your column in index table. So it will require bit additional time and memory throughout CUD operation. Then when it ought to be used .Imagine we produce a customer individually from User screen.So total customer at finish of day are a million. Now to search customer for whose goes to NewYork.here index will be very convenient. Agreed it'll decelerate the place customer a little, it will likely be fractionally bad, but performance we'll get throughout retrieval for brand new you are able to customer is going to be extremely good.

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

Your current conclusions are virtually ok.

Yes, for many queries, a catalog means less data blocks have to be read.

Yes, the default index key in Oracle is implemented internally utilizing a B-Tree.

Yes, there's some overhead for Create/Update/Remove procedures on the table with indexes - both when it comes to performance and space used - but this overhead is generally minimal, and simply justified once the improvement towards the performance of queries is recognized as.

I heartily recommend reading through the Oracle Concepts Guide on indexes.

Previous responds (as well as your conclusions) are correct. Regarding when you should use indexes, it may be simpler to go over if not to make use of indexes. Here are a handful of situations that might 't be appropriate to make use of a catalog.

  • A table that you perform a high-rate of card inserts, but never or rarely choose from this. A good example of this type of table may be some form of logging table.

  • A really small table whose rows all squeeze into one or a few blocks.

Indexes accelerate chooses.
Edge in the game by reduction of the amount of rows to check on.

I've got a table with 1,000,000,000 rows.
id is really a primary key.
gender could be either male or female
city may be one of 50 options.
street could be many different options.

When I am searching for a distinctive value, utilizing an index it will require 30 searches on the fully balanced tree.
With no index it will require 500,000,000 searches normally.
However putting a catalog on gender is pointless, because it won't lessen the search time enough to warrant the additional time required to make use of the index, research the products and than obtain the data within the rows.

For city it's a border situation. Basically have 50 different metropolitan areas a index is helpful, for those who have only 5 the index has low cardinality and won't get used.

Indexes decelerate card inserts and updates.

More stuff to think about
MySQL are only able to play one index per (sub) choose per table.
If you wish to make use of an index on:

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

You'll have to declare a substance index:

ALTER TABLE table1 ADD INDEX index_name (city, street)