Hi Craig!

Since I started to work with Interbase, I'm a bit lost about indexes.

In Paradox/dBase we defined manually each one, thinking about the searches
we would do or if we would need some kind of filtering or ordering using
tables.

But IB has the dynamic indexes it uses when it needs, and I don't know when
an index can be useful or even reduce speed...

I saw people saying that we should let IB define the indexes (we should only
define a primary key...), and other people saying to define some to increase
speed...

About your comment above, when should I define an Index, thinking about
speed on ordering and filtering?
--

        [][][][]

               Marcelo Carvalho.

        Indices give you one and only one important feature: Selectivity.  When
you need to decide if a particular index will be helpful for a
particular operation, you need to know the selectivity of the index and
the nature of the operation.

        What is selectivity?  It's a measure of how uniquely the index can
identify a particular record.  For example, the selectivity of a primary
key index is always 1, since the primary key always uniquely identifies
a particular record in the table.  In other words, since no two records
can have the same primary key value, any value in the index will always
correspond to exactly one record.

        The worst case selectivity is an index on a column in a table where
every record in the table has exactly the same value in that column.  In
this case, the index doesn't help you find a particular record at all,
and the selectivity of the index = the number or records in the table.

        Indices are very useful for WHERE clauses, because a very selective
index will significantly lower the number of records which must be
evaluated.  Imagine the following WHERE clause:

WHERE
  COLUMN1 = 'Foo'
    AND
  COLUMN2 = 'Bar';

        Imagine further that there are 100,000 records in the table, and that
there is an index on COLUMN1 with a selectivity of 5 -- on average,
there are 5 records in the table for every value in the index.

        Without using the index, InterBase would have to read all 100,000
records and see if each one meets the conditions in the WHERE clause.
Using the index, InterBase can immediately locate the records which
match the condition on COLUMN1 (there are probably around 5 of them) and
then test them to see if they also meet the conditions on COLUMN2.
This, obviously, is much faster.

        On the other hand, imagine if the selectivity on the COLUMN1 index was
50,000.  In this case, using the index would probably slow down the
search.  Why?  Because it's faster for InterBase to scan records in
NATURAL order (that is, the order in which the records are stored on
disk) because moving the disk drive read head takes a lot of time,
relatively speaking.  If the index is selective enough that using it
significantly lowers the number of records that must be scanned, then
it's worth reading the records out of order since we have to read so
much fewer of them.  But if we're going to read most or all of the
records anyway, it's faster to read them in whatever order they happen
to be in on disk than to read them in index order.

        This is why indices slow down ORDER BY clauses.  It turns out to be
faster to read the entire result set in storage order and sort the
records in memory than to read them out of storage order.  Even if the
records must be written to a temp file to perform the sort, it's still
faster than reading the records out of storage order, because InterBase
is fairly intelligent in the way it reads and writes temp files.  

        The exception to this is when you only intend to retrieve the first few
records from the result set.  In this case, using the index is faster.

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Hi Craig!

   Thanks a lot for your deep explanation!

   Despite indexes can slow down OrderBy clauses, what if we have both,
Where and OrderBy?
   I suppose that in this case, if the selectivity is high, the index should
be applied, to speed up the search. So OrderBy will work on lower records. I
suppose, of course, that OrderBy is performed after Where clause.
   So the primary analysis should be made to Where clause, right ? (except
if my app uses to retrieve lots of registers at once, not usual for Internet
apps...)

   What about the dynamic indexes on Interbase? I've heard that it generates
some indexes itself. Is it right?
    In this case, how can I know which indexes it created? If I create my
own indexes (following the criteria you explained), will I have duplicated
indexes?

   Is it possible to deactivate an index at the moment of a query? Is it a
fast operation?
   Like this I can activate and deactivate indexes depending on the kind of
query (OrderBy, Where, Selectivity of chosen fields, more or less records to
be retrieved...)

       Thanks again!

                [][][][]

                    Marcelo Carvalho.

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> escreveu na
mensagem news:3B9F7184.B62A60D6@no_spam.vertexsoftware.com...

        Most times the difference in an ORDER BY isn't that big, so if the
index significantly improves the WHERE, then use it.  If the difference
in the ORDER BY is too big, you can force IB to not use the index by
appending an empty string onto the end of a string column, or adding 0
to a number, i.e.:

SELECT
  FIELD1 || ''
FROM
  MYTABLE
ORDER BY
  1;

        Yes, but there's no need to guess.  The PLAN tells you exactly in what
order the query is performed, and whether or not an index is used for
each piece.

        Usually, yes.

        InterBase will automatically create an index on a PRIMARY KEY or
FOREIGN KEY constraint.  InterBase can also combine several indices when
it will help run a specific query.  It will not "generate" an index in
other circumstances.

        The PLAN tells you exactly which indices are used to perform a query.

        Don't do this.  It will affect other users as well.  Instead, rewrite
your query such that it does not use the index.

        -Craig

P.S.: Please don't quote my entire message when you respond to my post.
Thanks!

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Hello Craig!

As I see, any operation (arithmetic or string) disables the index... And
just for me (not for other users).

Sorry but... what is the "PLAN" ?

Well, if the dynamic indexes are used for specific queries they are
temporary. And Primary key and Foreign key usually we do create... So we
really need to be worried about creating the right indexes for frequent
where clauses!

Thank you once more!
--

        [][][][]

               Marcelo Carvalho.

        No, doing this has no effect on other users.

        It's a structured way of expressing how IB has optimized your query.
You see it in IBConsole under the ISQL statement window.

        There is no such thing as a dynamic index.  You either have an index on
a column (or columns) or you don't.  IB will choose whether or not to
use it depending upon whether or not it thinks using the index will make
the query run faster.

        Yes, this is very important.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Thank you Craig!

--

        [][][][]

               Marcelo Carvalho.