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
>
