At 1:19 -0600 11/6/02, D. Walton wrote:
At 01:05 AM 11/6/2002 -0600, you wrote:
Paul, the point was to have 'value' be part of the primary key for
pure lookup speed (data file would not need be referenced), but
not to have it effect the uniqueness of the 'id'/'date' key pair
so that I could do an 'insert ignore' into the table with a
'value' of 0 and if a record with matching 'id'/'date' already
existed with a 'value' of 4 then it would not create another
record. I could do this with two indexes but using two indexes
would more than negate the benefit of not having to lookup in the
data file. Yet it's perfectly reasonable to have mysql use the
same index for both of these index definitions, but from Jeremy's
post it appears that mysql won't do this optimization.
From Jeremy's post, no such thing appears. He answered your question
correctly. You were just asking the wrong question. :-)
Use a single three-column index. Drop the two-column one and create
a new one with the third column added.
Paul, I'm not sure I understand. Are you agreeing with Jeremy or
are you saying there is a solution, but I didn't ask my original
question correctly? I'm afraid I don't see how using a single
three-column index solves this? If I use a single three-column
unique index then I can potentially add two rows with the same
'id'/'date' pairs so long as they both have a different 'value'. I
need for no two records to have the same 'id'/'date' yet I also need
the 'value' in the index for maximum lookup speed.
Okay, I didn't understand that last part properly. Sorry.
If you want to enforce uniqueness at the id/date level, you will
need a separate unique index on just those two columns, in addition
to a three column index on id/date/value. There is no syntax for
specifying that you want an index on a set of columns but to enforce
a uniqueness constraint on just a subset of those columns.
-Dan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
