Hi, I've been bashing my brains out for a day or two on this, so I'd
appreciate some pointers.
When altering a table to add indexes I get a message like :
error 1034: 126 at record pos 957847618
mysql -V tells me:
mysql Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386)
myisamchk -dv name_index2 tells me:
myisamchk -dv name_index2
MyISAM file: name_index2
Record format: Fixed length
Character set: latin1 (8)
File-version: 1
Creation time: 2003-04-01 13:32:18
Recover time: 2003-04-01 14:26:25
Status: checked
Data records: 13844139 Deleted blocks: 0
Datafile parts: 13844139 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 1190595954 Keyfile length: 522063872
Max datafile length: 369367187454 Max keyfile length: 17179868159
Recordlength: 86
table description:
Key Start Len Index Type Rec/key Root
Blocksize
1 2 4 multip. unsigned long 0 412131328
1024
6 4 unsigned long 0
2 11 20 multip. char packed stripped 0 97328128
1024
55 20 char stripped 0
80 3 char 0
show create table name_index2 tells me the create statement is:
CREATE TABLE `name_index2` (
`dataset_vers_id` int(10) unsigned NOT NULL default '0',
`row_id` int(10) unsigned NOT NULL default '0',
`quality_level` tinyint(4) NOT NULL default '100',
`surname` char(20) NOT NULL default '',
`emanrus` char(20) NOT NULL default '',
`surname_id` int(10) unsigned NOT NULL default '0',
`forename` char(20) NOT NULL default '',
`forename_id` int(10) unsigned NOT NULL default '0',
`forename_pos` tinyint(4) NOT NULL default '0',
`place_code` char(3) NOT NULL default '',
`start_year` smallint(6) NOT NULL default '0',
`end_year` smallint(6) NOT NULL default '2100',
KEY `name_index_idx1` (`dataset_vers_id`,`row_id`),
KEY `name_index_idx2` (`surname`,`forename`,`place_code`)
) TYPE=MyISAM
I've been adding the indexes one at time to try and isolate the problem,
the failure occurs when I add:
alter table name_index2
add index name_index_idx3 (surname_id, forename_id, place_code);
Possibly important: 744845 data records (~ 5%) have a surname_id = 0
I'm using the 'large' my.cnf from the distribution with a couple of
tweaks.
A couple of weeks ago, a 10% smaller table took 45 minutes to add the
indexes. This one an hour to add two indexes and several hours to fail
to add all four.
I'm getting to the stage when I'm considering MERGE tables.
Any ideas welcome!
--
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
