Re: Using an ENUM column in an index

[Tom Byars]

Note horseID,raceID is in itself a unique index (a horse can't be in the same race twice) but other indexes such as trainerID,raceID,Ord and ownerID,raceID,Ord need the Ord column as trainers/owners can have more than one runner in a race. The reason I added the Ord column to the horseID,raceID,Ord index was so that I would be able to group_concat a series of form figures without MySQL having to look beyond the index e.g.

select group_concat(
if(left(Ord,2)>'40',left(Ord,1),
if(left(Ord,2)<='09',mid(Ord,2,1),'0'))
separator '')
from Runner where horseID=hID and raceID<rID
order by raceID desc limit 10;

would return up to the last 10 form figures for horse hID prior to race rID
e.g. '16430PF721'
(hopefully) without having to even access the Runner table as all the information is present in the index.