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