Re: How does indexes work ?

indexes does creates another table, callled an index table, containing only those columns in the index and an internal pointer with the direct address of the row(s) which does satisfies the index constraints (equijoin, thetajoin, etc). each vendor does has proprietary methods for storing indicies, usually modified versions of b-trees, c-trees, t-trees, etc. which makes searching O[log2(n)] where n is the number of data rows in the data table.

if you would like to know more, you can search the net or find a good book on the subject. perhaps someone here in the newsgroup does has a good reference for you.

"Diego TERCERO" <diego.tercero_at_excite.com> wrote in message news:LCze7.1654$eZ6.991378_at_nnrp4.proxad.net...
> Hi,
> I've always wondered something about indexes and I wanted to know if any
 of
> you knew the answer.
>
> I know that putting indexes on some fields in a database can be crucial to
> speed up the time of response in a query. But I don't clearly understand
 how
> this work.
>
> I understand that in a database in which you have let's say 3000 rows,
 when
> you search a specific record based on a field that has no index on it you
> usually have to scan about 1500 rows (a mean : 3000 / 2) until you find
 the
> record you're searching. If you have an index on it, there's no scan, you
 go
> immediately to the row you're searching.
>
> Now... how that works ?
>
> Example. I have a database like this
> employees[id, first_name, last_name, adress, security_number];
>
> id is the primary key, then.. it has an index automatically.
> Let's say I don't put any index anywhere else.
>
> Let's say that I do this
> SELECT first_name
> FROM employees
> WHERE security_number = '123A52DBD'; // The DBMS is going to scan al the
> records.. I understand this
>
> Now let's say I do this
> SELECT first_name
> FROM emplyees
> WHERE id = 10231;
>
> What is the DBMS going to do ?
> Does it have an internal table where it keeps the correspondence between
> index values and their memory adress ?
> But even then... how is this internal table organized ? How come when
 having
> a specific index value (e.g. 10231) the DBMS finds it immediately in the
> internal table and does not have to scan for it ? For me having an
 internal
> table would only push the problem further : you still have a table with
> index values and need to find a specific value in it... so it's just like
> starting all over again, no ?
>
> Anyone can explain this to me ?
>
>
>
Received on Wed Aug 15 2001 - 21:29:37 CEST