Jigal,
>I would define the key as: parameter_name-value-account_id.
>InnoDB is very fast if you use the primary key and a lot slower
>if you use secudary key(s), so queries can get considerably faster
>if you use a primary key.
One reason the PK is faster is that the engine needn't handle NULLs in
it.
>My combined key would be able to uniquely identify records.
What!?? NULL has no length. In key values like stringvalueNULLintvalue,
what position in the key value is the engine supposed to consider the
beginning of intvalue!?
>I know the SQL standard defines a PRIMARY KEY as a combination
>between UNIQUE and NOT NULL, but it's still not clear to me why
>this implies that all *parts*
of the primary key *must* also have the
>NOT NULL constraint.
Allow NULLs as PK components and you destroy two things at once, PK
logic and performance.
PB
-----
Jigal van Hemert wrote:
From: "Paul DuBois"
I realise that it may (and is) defined in such a way, but it still does
not
explain *why* part of a PRIMARY key might not be NULL. If the combination
of
parts in the PRIMARY key is such that it can uniquely identify a record
it
would be sufficient for a primary key IMHO. It could well be a UNIQUE
index
with the restriction that the complete key (the parts combined) may not
be
NULL...
I'm not sure I understand what you are saying. But if part of a PRIMARY
KEY
could be NULL, then it _wouldn't_ uniquely identify records.
It would IMHO if the other parts combined would be unique.
In this case it's a table that contains account_id, parameter_name and
value.
Account_id and parameter_name would be sufficient to uniquely identify a
records (only one parameter with the same name per account allowed).
But since searches use the parameter_name/value combination in almost all
cases I would define the key as:
parameter_name-value-account_id. InnoDB is very fast if you use the primary
key and a lot slower if you use secudary key(s), so queries can get
considerably faster if you use a primary key.
My combined key would be able to uniquely identify records. I know the SQL
standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL,
but it's still not clear to me why this implies that all *parts* of the
primary key *must* also have the NOT NULL constraint.
Regards, Jigal.
|
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
