[thelist] SQL Indexes

[rudy]

> 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