MySQL Performance: MySQL => large table

large table - index creation [message #2226]

Tue, 20 November 2007 11:13

andyr
Messages: 1
Registered: November 2007
Location: Ithaca, NY

Junior Member

Hi,
I am running MYSQL version 5.0.24 and have a table with over 100 million records. A slimmed down version of the InnoDB table looks like the following:

Individual_id varchar(10),
Family_id varchar(10),
Value varchar(100)

The goal was to create a table that could be used to generate a list of values by individual and by family id. So, each of the following queries would produce the list of values that I’m looking for.

select group_concat(value)
from table
group by individual_id

example: individual1, value1, value2, value3,….,valueN

select group_concat(value)
from table
group by family_id

example: family1, value1, value2, value3,….,valueN

I’ve imported all the data but it is taking a very long time to create indexes on the Individual_id and Family_id columns. Individual_id has a cardinality of 20,000 while family_id has a cardinality of ~ 4000. The index creation has been running for over 4 hours. Can anyone suggest a more efficient way to approach the problem of index creation? In hindsight, I probably would have converted the individual_id and family_id to an integer value? I’m wondering if I should cut my losses and make that switch before creating the indexes?

Thanks in advance.

Report message to a moderator