Narrow and Fixed Indexes

[1dg618]

What is an index that is narrow and fixed?

int
int
int
datetime

be narrow and fixed?

Would

int
int
char(4)
datetime

be narrow and fixed?

And

int
int
varchar(25)
datetime

would be narrow but not fixed?

[b***@msn.com]

Post by 1dg618
What is an index that is narrow and fixed?
int
int
int
datetime
be narrow and fixed?
Would
int
int
char(4)
datetime
be narrow and fixed?
And
int
int
varchar(25)
datetime
would be narrow but not fixed?

Depends on your definition of narrow, I suppose, and that could be
relative to
the total row width. An index that is 100 bytes wide might be
considered narrow
for a table with rows that are 4500 bytes wide, but not for a table
that is 200 bytes
wide. Are you referencing these terms from some particular source?


I would expect that the difference between fixed and not fixed would
be whether any
of the keys were variable length or nullable. datetime would be
fixed, datetime null
would be not fixed.

[Derek Asirvadem]

On Oct 19, 11:16 am, 1dg618 <***@gmail.com> wrote:

Yes.

By "narrow" I meant any number of narrow columns: 1 & 2

By "fixed" I meant all the columns are fixed length *and* not null.
Nullable is implemented as variable length.

In the context of making general statements about how slow/fast an
index is, without observing other aspects such as no of rows; APL vs
DPL/DRL; row size; fragmentation; etc, a "fast" index is one that has
narrow, fixed columns. The number of columns does not make much
difference, the width of the columns, and the fixed/var does make a
difference.

[1dg618]

Post by Derek Asirvadem
Yes.
By "narrow" I meant any number of narrow columns: 1 & 2
By "fixed" I meant all the columns are fixed length *and* not null.
Nullable is implemented as variable length.

I didn't know it. Good info.

Post by Derek Asirvadem
In the context of making general statements about how slow/fast an
index is, without observing other aspects such as no of rows; APL vs
DPL/DRL; row size; fragmentation; etc, a "fast" index is one that has
narrow, fixed columns.  The number of columns does not make much
difference, the width of the columns, and the fixed/var does make a
difference.

Thank you. That's really good information.