Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
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. –  ypercube 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. –  ypercube May 29 '12 at 11:54

2 Answers 2

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

share|improve this answer
    
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. –  Aaron Brown 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. –  RolandoMySQLDBA 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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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