Re: Using an ENUM column in an index
Posted by:
Tom Byars
Date: March 18, 2015 01:58AM
Rick James Wrote:
-------------------------------------------------------
> You may as well make ENUMs "NOT NULL".
>
> Let's see some of your indexes and the SELECTs
> that 'need' them.
Rick, I have made it not null. It's a horse racing database I'm working on and I have an index horseID,raceID,Ord. The Ord column is the enum column which incorporates the post position of the horse along with a tie breaker for dead heats. Thus the enum column is along these lines
ENUM('0101','0102','0103','0201','0202','0203',.....,'4001','4002',.....,'PU01',...,
PU20,....)
[PU as in pulled up].
I'm aware this could cause problems if e.g. more than 3 horses dead heat or more than 20 horse pull up etc. but I could always change the enum to accommodate a freak occurrence. Anyway, I'm only experimenting just now to see how fast I can e.g. retrieve a horse,jockey,trainer,owner's history.