NULL fields in table - efficiency question

I have read it is better to always have all the fields in a table set to
NOT NULL as this makes it much quicker.


In one case I have a table where all fields qualify for this accept for one
which is comments. This will only sometimes be filled.

Am I therefore better off making this field still to be Not NULL and set the
default value to a single arbitrary character such as "." or is this
actually making things less efficient.



Input welcomed. Thanks in advance.

Re: NULL fields in table - efficiency question

Hello,

David Smithz wrote:

> I have read it is better to always have all the fields in a table set to
> NOT NULL as this makes it much quicker.

You did not say what DBMS you are using, however I dont think that NOT NULL
will make things faster on any system. If you want speed, use indexes, and
a DBMS with a good query optimizer.

> In one case I have a table where all fields qualify for this accept for
> one which is comments. This will only sometimes be filled.
>
> Am I therefore better off making this field still to be Not NULL and set
> the default value to a single arbitrary character such as "." or is this
> actually making things less efficient.

You should set all fields that must be filled out to NOT NULL, just to
enforce this. And using "." instead of NULL is not really useful. After all,
if there is no comment, NULL would be a wise choice for the field value.


Boris Stumm

Re: NULL fields in table - efficiency question

"Boris Stumm"

thanks for the input Boris. The DB is MySQL (actually I thought this was a
mysql ng but I see now it is not only MySQL).

I just thought I remember reading that a table with all not null values is
very efficient over one that can have null values but perhaps I am wrong
about this.

Thanks again and any other opinions welcome.

Re: NULL fields in table - efficiency question

David Smithz wrote:

>
> "Boris Stumm"
>
> thanks for the input Boris. The DB is MySQL (actually I thought this was
> a
> mysql ng but I see now it is not only MySQL).
>
> I just thought I remember reading that a table with all not null values
> is
> very efficient over one that can have null values but perhaps I am wrong
> about this.
>
> Thanks again and any other opinions welcome.

I have no idea, ask the good people at comp.databases.mysql, they might
know more.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

Re: NULL fields in table - efficiency question

Post removed (X-No-Archive: yes)