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