I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same primary key for the original record ).
In the new record there is a reference to the original record.
So to query the last record for a specific customer/user we need to do the following.
select * from where business_key = 1234 and parent = id
I want add an index on business key does it make sense to make the index a multi-column index?
ALTER TABLE MyTable ADD INDEX NewIndx ( business_key ASC, parent ASC, id ASC )
Will the parent part in the index be used, because it is referencing the id and a constant?
id | parent | data 1 | 1 | Last record 2 | 1 | previous record of 1 3 | 3 | Other record