From: Gleb Paharenko (gleb.paharenkoensita.net)
Date: Mon Oct 03 2005 - 09:43:28 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hello.

> What's the reason behind this?

If you're asking about why I've written that columns defined as NOT NULL
are faster - I've read it in one of the articles from dev.mysql.com.
I'm not sure about the true reason, but the way MySQL stores NULLs
is strongly dependent on the storage engine. Dig in documentation which
is available in MySQL development tree about the physical structure of
the index for different storage engines.

Jigal van Hemert wrote:
> Gleb Paharenko wrote:
>
>> I'm not giving an exact answer on your question, however, it might be
>> interesting for you. Usually queries are faster if you define the
>> column as NOT NULL.
>
>
> What's the reason behind this? NULL 'values' are a bit of strange
> phenomenon. In the EXPLAIN output a query with WHERE <col> NOT NULL; is
> of type 'range', which implies that NULL has a position in the range of
> values of the column. On the other hand UNIQUE indexes allow multiple
> NULL 'values' (except for BDB tables) and the storage space for various
> data types does not leave room for an extra 'value' in the range.
>
> It almost seems as if NULL is stored as a kind of prefix in an index?
>
> Regards, Jigal.
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __ ___ ___ ____ __
  / |/ /_ __/ __/ __ \/ / Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenkoensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
       <___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]