Jigal, 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. PB ----- Jigal van Hemert wrote: From: "Peter Brawley" Peter,>Anyway, I gues it's just a question of following a standard andoptimizing>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. |
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]