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
