| 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.
|
|
|
|
|
| Re: large table - index creation [message #2256 is a reply to message #2251 ] |
Thu, 22 November 2007 20:22  |
mikec
Messages: 22
Registered: September 2007 |
Junior Member |
|
|
slight correction...f MyISAM, make sure myisam_sort_buffer
is also big.
|
|
|
Members
Search
Help
Register
Login
Home
