MySQL indexes causing severe slowdown

Anon

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

Anon

I believe indexing begins to give you greater returns when you have multiple tables and multiple indexes. Doing selects with just one index on a freshly loaded table isn't a very good test for indexing.

[deleted]

Ofcourse it's going to slow loading down, you are telling it to create an index while you are inserting, that's a lot of extra work, so it has to be slower.

Also, you are making it index on the full 200 chars of field3, that means it pretty much re-creates the entire column, that's 446771 extra fields that need to be sorted while they are inserted etc...

And finally, the very point of an index is to speed up your SELECT statements, not to speed up inserts. Indexes are not used during inserts, they are only updated.

BTW, how long do your queries take if you say that the increase is "only
0.3 seconds per query?
I think that once you get the indexes sorted out properly, you'd be amazed at their usefulness.

oh, and 446771 rows, times 0.3 seconds per query is a speed increase of 134031 seconds, or one and a half day. Sounds usefull to me! :-)

Anon

OK obviously I need some training in this index field. So you suggest I create the table, then add an index? I thought the index HAD to be of the length of the field you were indexing? How else would I make it? I tried creating an index AFTER loading the database and it took a long time too.

I understand that indexes are used for SELECT's, but I was just saying that it was taking forever to create the index on 400,000 rows.

What do you recommend I do in this case?

Mike

[deleted]

The MySQL manual explains how to create indexes:

http://www.mysql.com/doc/C/R/CREATE_INDEX.html
http://www.mysql.com/doc/A/L/ALTER_TABLE.html

Creating an index means fetching data from the table and building a new table that contains lots of info about the original table.
This cannot be done in a hurry!

The index on a column only needs to be as long as you need.
For example, if you are looking for strings that start with the letter "A", your index would only have to be on the first character of the column.
But if you are going to sort, you'd need more characters.

Creating an index on 400k rows is slow, because it is a lot of work. Nothing can be done about that except streamlining your index, only index what you need.
And ofcourse, brute force. If you want a fast database, your database server needs resources. 400k rows is a lot of data, your server needs lots of memory to deal with that.