MySQL using a index referencing a different column

Question

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?

Sample records

id | parent | data
1  | 1      | Last record
2  | 1      | previous record of 1 
3  | 3      | Other record

Answer 1

According to your question, you use select * from MyTable where business_key = 1234 and parent = id; to get the last record.

There is caveat you need to be aware of with regard to the index. If a particular business_key has 1000 records, the query will do a index range scan through all 1000 key entries. Since you know that parent=id indicates the last record, it would plausible for you to change the query to take advantage of that. Why? The index you would automatically have the ids sorted for any given business_key,parent combination. With that in mind, please change two things

CHANGE #1 : Refactor the Query to the following:

select B.* from
(
    select MIN(id) id FROM MyTable
    where business_key = 1234
) A INNER JOIN MyTable B using (id);

Keep in mind that MAX(id) would be the original (the oldest) record.

CHANGE #2 : Add the following index

ALTER TABLE MyTable ADD INDEX NewerIndx (business_key,id);

If you have queries that have this WHERE clause (WHERE business_key=1234 AND parent=999), then do not drop NewIndx. You may want to drop your old index on a staging or dev server and test all your queries, if any of the queries get worse on the dev server, keep your old index.

Give it a Try !!!

Answer 2

Plan A: Current data to the current table; old stuff in another table. The "old" table would have extra column(s) to keep track of age, reason for archiving, whatever. Advantage: The 'important' queries read the 'current' data, without stumbling over the old stuff.

Plan B: Rethink the schema into: (1) current data an (2) log of changes. This is very similar to Plan A, but here the "log" would contain an entry for the original insertion into the 'current' table.

As for the NewIndx -- it is difficult to say anything without seeing (1) the current CREATE TABLE, and (2) the SELECT that it should help.