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
  • 2
    Wouldn't it be better to keep old rows in a separate table?
    – dezso
    May 29 '12 at 10:48
  • No. If we just keep the records for auditing purpose then you would be right. But the versioning is used (abused) in our business application. I am not allowed to change the business application at this time.
    – Jake
    May 29 '12 at 11:14
  • Do you have a datetime or timestamp column that you can use for identifying the "active" record? It would be easier to find a query and good index with that. May 29 '12 at 11:20
  • @ypercube you are a genius ( or I am a idiot ). We have an creationdate, startdate and enddate. The enddate is always null for a parent record. Which makes it perfect indexable :)
    – Jake
    May 29 '12 at 11:49
  • I'll accept the first (part about genius). I think the parent=id will not be fast. It will use the index you suggest but not efficiently. May 29 '12 at 11:54

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

  • Change #1: This is something I will try.
    – Jake
    May 30 '12 at 7:15
  • Change #2: id is the primary key, shouldn't the optimizer do an index_merge to do the same?
    – Jake
    May 30 '12 at 7:16
  • There is no need to include the primary key in composite indexes. Jun 29 '12 at 1:32
  • 1
    @AaronBrown I think you are right because all secondary indexes carry a reference back to the Clustered Index anyway. Good thinking. Jun 29 '12 at 2:22

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.

  • Sadly this isn't possible. The versioning system in the application is (ab)used. In some of the tables the history records are used for tracking if a customer has spend a credit on action. So that the customer doesn't have to spend another credit for the same action. IMHO this is not supposed to be done by using the versioning system. But it is there and at the moment I am not in the position to change this.
    – Jake
    May 30 '12 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.