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

>
>Seriously, it seems like there should be an optimizer in there that 
>could pick out the fact that the second index is simply a subset of 
>the primary key.  It's very simple logic, even if this situation 
>very seldomly occurs.
>
>-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