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
