MySQL using a index referencing a different column

index-tuningMySQL

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

Best Answer

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

Related Question