Force 'Replace Into' to use a certain index
- by Bobby
I have a MySQL (5.0) table with 3 rows which are considered a combined Unique Index:
CREATE TABLE `test`.`table_a` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field2` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field3` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`Id`),
INDEX `IdxUnqiue` (`field1`(5),`field2`(5),`field3`(5))
) ENGINE=MyISAM;
This table should be filled with a REPLACE INTO query:
REPLACE INTO
table_a
(
Field1,
Field2,
Field3
)
VALUES
(
"Test1",
"Test2",
"Test3"
)
The behavior I'd like to see is that this query always overrides the previous inserted row, because IdxUnique is...ahm, triggered. But unfortunately, there's still the primary index which seems to kick in and always inserts a new row.
What I get:
Query was executed 3 times:
+---Id---+---Field1---+---Field2---+---Field3---+
| 1 | Test1 | Test2 | Test2 |
| 2 | Test1 | Test2 | Test2 |
| 3 | Test1 | Test2 | Test2 |
+--------+------------+------------+------------+
What I want:
Query was executed 3 times:
+---Id---+---Field1---+---Field2---+---Field3---+
| 3 | Test1 | Test2 | Test2 |
+--------+------------+------------+------------+
So, can I tell REPLACE INTO to use just a certain Index or to consider one 'more inportant' then another?
