List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 6 2002 7:05am
Subject:Re: index question
View as plain text  
At 23:31 -0600 11/5/02, D. Walton wrote:
>At 10:32 PM 11/5/2002 -0600, you wrote:
>>At 20:39 -0600 11/5/02, D. Walton wrote:
>>>At 05:18 PM 11/5/2002 -0800, you wrote:
>>>>On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
>>>>>
>>>>>  I have a table with 3 fields, 'id', 'date', and 'value'.  I've
> created a
>>>>>  unique index on 'id' and 'date' in order to lookup 'value' quickly. 
> I
>>>>>  would like to be able to add 'value' to the index so that the data
> files
>>>>>  does not have to be referenced and will allow faster lookups 
>>>>>and groupings
>>>>>  by date, however, I can't lose the ability to do "insert ignore" on
> the
>>>>>  'id' and 'date' unique index.  So the question is, if I create a
> primary
>>>>>  key of 'id', 'date', 'value', and then create a secondary unique
> index of
>>>>>  'id' and 'date' will MySQL simply reuse the primary key for the
> secondary
>>>>>  unique index or will it create a totally separate index on the
> disk?
>>>>
>>>>It will create a totally separate index, since that's what you told it
>>>>to do. :-)
>>>
>>>Well, in that case, how do I "tell" it to do what I want it to do? ;-)
>>
>>In that case, what's necessary is for you to realize that you need do
>>nothing. :-)
>>
>>If you have an index on id, date, and value, then id, date is a leftmost
>>prefix of that index, and MySQL will happily use it.  You need not create
>>an explicit index on id, date.  Nor on just id.
>
>
>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.

>
>-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