MySQL Forums
Forum List  »  General

Re: Using an ENUM column in an index
Posted by: Tom Byars
Date: March 19, 2015 05:15AM

Peter, can you take a look at this below and see if I'm grasping what you're meaning please.

Suppose I have a UNIQUE index trainerID,raceID,Ord where Ord is as described above (i.e. a unique enum value describing a horse's finishing position).

If I want the trainer history for trainerID=tID prior to his runner in raceID=rID with finishing position Ord=Od I would issue the statement

select * from RunnerTbl
where trainerID=tID and (raceID<rID or raceID=rID and Ord<Od);

[Note - the right hand side of the 'or' covers cases where the trainer has more than 1 runner in the race.]

What you're saying is if I just have a NON-UNIQUE index on trainerID,raceID then, when executing the above statement, MySQL only has to check Ord in those rare cases where the trainer has more than one runner in race rID which makes adding Ord to the index a waste of time. If that's what you're meaning then, having thought about it, you're correct and I thank you for making me look at these things in a new light.

What I'm now wondering though is how much raceID is needed in some of my indexes. In the case of trainerID,raceID some trainers might have 20,000+ runners in the 2.5 million record Runner table in 20,000 different races so I'd assume raceID was needed there.

What about the index for the horse history though (horseID,raceID)? The horses in the database will have minimum runs 1, median runs 6, average runs 11 and maximum runs 230+. How would that fit in with your earlier statement regarding index selectivity? I would often be issuing the statement

select * from Runner where horseID=hID order by raceID;

so would the index already having them sorted in raceID order by itself justify adding raceID to the horseID index?

Options: ReplyQuote


Subject
Written By
Posted
Re: Using an ENUM column in an index
March 19, 2015 05:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.