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
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
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!