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:
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.
group by individual_id
example: individual1, value1, value2, value3,….,valueN
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.