List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 6 2002 3:43pm
Subject:Re: index question
View as plain text  
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

Thread
index questionD. Walton6 Nov
  • Re: index questionJeremy Zawodny6 Nov
    • Re: index questionD. Walton6 Nov
      • Re: index questionPaul DuBois6 Nov
        • Re: index questionD. Walton6 Nov
          • Re: index questionPaul DuBois6 Nov
            • Re: index questionD. Walton6 Nov
              • Re: index questionPaul DuBois6 Nov