Re: index question

[D. Walton]

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? ;-)

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



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php