Create index takes more than 5 hours

[James Johnson]

Greetings!

I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM.

I've created a table and imported 27 million rows of data.

I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made.

The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table.

Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB).

I'm wondering if this is normal time and space consumption for 27 million rows.

Finally, doing an un-indexed query on this table :

SELECT * from historical where reference = 'abcd';

Takes more than 1/2 hour. Is this also a normal amount of time?

I understand that people are using MYSQL for data warehousing and major transactional applications.

With this in mind, I'd like to know what I need to do to speed up the create index and table scan queries.

Thanks, all.

JDJ