Board index » delphi » Help with IB indexes

Help with IB indexes

I have an Interbase 5.5 database and need to improve performance on
routine selects.  I've tried creating indexes on the 3 fields that get
selected (both together and individually) but I get an error that says
"key size too big for index".  These are varchar fields and the smallest
is 255.  Is there anything I can do here either with or without an
index?  Thanks...
 

Re:Help with IB indexes


Quote
Keith Selbee wrote in message <37ED224F.3DD15...@babcox.com>...
>I have an Interbase 5.5 database and need to improve performance on
>routine selects.  I've tried creating indexes on the 3 fields that get
>selected (both together and individually) but I get an error that says
>"key size too big for index".  These are varchar fields and the smallest
>is 255.  Is there anything I can do here either with or without an
>index?  Thanks...

255 is the absolute maximum for a single index key, so you'll need to put
separate indexes on these fields. If the average size of the actual data in
these fields is quite large than there's only so much you can do for
performance, the server has to do a lot of of work.

The most important thing you can do is to make sure your queries can
actually use the indexes. Do not use the LIKE or CONTAINING keyword in your
queries, use comparative operators (=, <, >) or STARTING WITH.

Other things you might do are to adjust the Page Size for the database. The
default is only 1024 but for Windows (at least) 4096 is much better.

Also, Check out "How to make IB scream":
http://www.interbase.com/talk/q46.html  (download the Acrobat article)

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Bill of NO Rights; ARTICLE VIII:  You DON'T have the right to demand that
our children risk their lives in foreign wars to soothe your aching
conscience.

Other Threads