At 01:05 AM 11/6/2002 -0600, you wrote:
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.
Paul, I'm not sure I understand. Are you agreeing with Jeremy or are you saying there is a solution, but I didn't ask my original question correctly? I'm afraid I don't see how using a single three-column index solves this? If I use a single three-column unique index then I can potentially add two rows with the same 'id'/'date' pairs so long as they both have a different 'value'. I need for no two records to have the same 'id'/'date' yet I also need the 'value' in the index for maximum lookup speed.
-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
