MySQL & SELECT DISTINCT slow

[Anon]

I have encountered a problem that has me stumped. I have the following table:

TABLE trains

m_trainid varchar(6),
train_date date,
train_time time,
type=ISAM

m_trainid is an non-unique index

If I issue the following SQL:

SELECT DISTINCT m_trainid from trains;

The amount of time it takes to return is proportional to the number of records in the table. This doesn't make sense to me. It should look at the index for this field and return distinct values instantly. If I only have 10 unique m_trainid entries, it shouldn't matter how many observations of each train I have in the table should it?

Anybody out there got any ideas?

[Anon]

It might seem odd, but in this case the index is not helping you. I doubt you would notice a performance dip by dropping the index and executing the query, as a sequential scan is the norm here. BTW, mysql is much faster at this than most databases.