I think MS-SQL and Oracle provide switches for treating NULLs as values, which is what your proposal amounts to. It seems to me that much of the performance advantage you are counting on from PKs would go away if PKs could have NULLs and if NULL were a value. IAC I hope MySQL doesn't go that way.



Jigal van Hemert wrote:
From: "Peter Brawley"


 >Anyway, I gues it's just a question of following a standard and
 >the engine according to that standard.

Your proposal would permit dupe primary keys. It's a question of
preventing them.

Sorry, but I disagree.
If NULL handling is not done by the table engine but by the rest of MySQL
then MySQL can compare two NULLs and can act in different ways depending on
the situation (UNIQUE index in BDB can only have a single NULL entry, but
MySQL allows multiple NULLs in other table types)

So, if we would define that the key entry "0-NULL-Whatever" equals
"0-NULL-Whatever" (which MySQL is capable of if you look at the definition
of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key
entry would not permit duplicate entries. At least not more than allowing
other values.

Because there is a considerable performance difference between primary and
secudary keys in InnoDB it would enable more flexible primary keys that can
also be used for searching.

It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so
it's not likely that it will ever be introduced ;-)

Anyway, I need NULLs in the db (for various reasons which we haven't been
able to solve in other ways) but I need fast searching on name/value. So I
have to figure out a different method.

Thanks for thinking about this!

Regards, Jigal.