[thelist] SQL Indexes
rudy
Rudy_Limeback at maritimelife.ca
Fri Oct 13 12:41:12 CDT 2000
> I'm waiting to see what poetry Rudy has for us on this one.
> Hold on kids, if he's in the mood, it'll be a doosy =)
i'm always in the mood
;o)
i really liked adam's description of b-tree indexes
<tip type="SQL indexes">
generally speaking you will not need indexes on tables that have few rows
the definition of "few" varies with the particular table, the size of its
rows, and also on the "industrial strengthiness" of your database system
(you want a database optimizer that is smart enough to know when accessing
the index will *not* be faster than accessing the entire table)
in addition, it would not make sense to index a table that isn't accessed
repetitively -- a good example would be a query that is run only once when
a cold fusion server session is initiated, and kept in cold fusion query
cache for reuse
if you think you need an index, remember that having an index on a table
automatically means you incur a performance penalty on inserts and deletes
(and some updates), but it means selects are typically faster -- in some
instances, like in complex queries and large tables, astoundingly faster
if you don't do inserts and deletes (and index-affecting updates) very
often, or if you do them offline, then the performance increase you get on
selects makes having indexes worthwhile
one rule of thumb (your mileage may vary): don't create indexes all over
the place just because you can
that said, it's often useful to have indexes on id numbers and columns
which are used in table joins
some databases automatically create a unique index on the column(s) that
you define as the primary key of a table
remember, the only reason you declare primary keys should be so that you
can reference them in foreign key declarations, i.e. you want the database
to enforce relational integrity
so if you aren't going to turn on referential integrity, you won't need
foreign keys, so you don't need primary keys either, except that sometimes
it's more convenient to declare primary keys than declare the unique
indexes that the database gives you for primary keys (i hope that made
sense)
one other important type of index is called a "covering" index because it
"covers" the column requirements for a specific query
for example, if you have
select B, C
from yourtable
where A='foo'
then an index on A,B,C "covers" all the columns in that query,
consequently the database can use the index only, and doesn't need to
access the table
the same index *might* be used for a query like
select B, D
from yourtable
where A='foo'
if only to narrow the search down based on accessing the first column of
the index to find the "foo" rows, then accessing the table rows to find
the values of D
the same index would probably *not* be used for a query like
select A, D
from yourtable
where C='foo'
here the database would have to read the entire index to find the index
entries that qualify, because C is in the lowest position in the index --
and then it would have to go to the table anyway
when you extend the "covering" concept to the point where you have an
index on *all* the columns, then you don't actually need the table at
all!!!
this is sort of (in a very gross way) the idea behind bitmapped databases
like Sybase IQ, all the data is in the indexes and the tables don't really
exist -- except you still write your sql as though they did
</tip>
i could go on about this stuff for hours but we are really far afield from
the mainstream of web development topics, eh?
rudy
r937.com
More information about the thelist
mailing list