Why is it that an index on a table seems to slow loading exponentially?
I have a table like this:
create table mytable (
id INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
field1 CHAR(16), field2 VARCHAR(128),
field3 VARCHAR(200), primary key(id),
index i_field3 (field3(200)));
1) I load the data, containing 446,771 rows, into a table, and it takes 1599.74 seconds (26.6623 minutes) to complete its operation.
2) I load the data again into a new table WITHOUT the index i_field3. Running the same operation takes 126.20 seconds (2.1033 minutes).
The performance change in select'ing data from the two tables only gives the indexed table an advantage of something like .3 seconds. What is the point of having an index if loading data into it takes sooooo much longer? I must be doing something wrong here, someone please show me what to do.
Thanks,
Mike
I have a table like this:
create table mytable (
id INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
field1 CHAR(16), field2 VARCHAR(128),
field3 VARCHAR(200), primary key(id),
index i_field3 (field3(200)));
1) I load the data, containing 446,771 rows, into a table, and it takes 1599.74 seconds (26.6623 minutes) to complete its operation.
2) I load the data again into a new table WITHOUT the index i_field3. Running the same operation takes 126.20 seconds (2.1033 minutes).
The performance change in select'ing data from the two tables only gives the indexed table an advantage of something like .3 seconds. What is the point of having an index if loading data into it takes sooooo much longer? I must be doing something wrong here, someone please show me what to do.
Thanks,
Mike