Bug #35980 CREATE INDEX on a varchar field on a large table takes a very long time
Submitted: 10 Apr 2008 23:49 Modified: 21 May 2008 19:16
Reporter: David Dick Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.51a OS:Linux (Fedora 8 and SLES 9 at least)
Assigned to: Paul DuBois CPU Architecture:Any

[10 Apr 2008 23:49] David Dick
Description:
when attempting to create a varchar index on a already large table, the operation takes a much longer time (eg 40 mins) as distinct from creating an index on a int column (eg 1 min).  This is made worse by the fact that if a varchar index is already in existence and a ALTER TABLE statement is issued, a similar length of time is required, i suspect because a alter table involves dropping indexes and recreating them.  All of a CPU instance is consumed during the operation, but memory and disk do not seem unduly effected.

How to repeat:
create a large table with a varchar field.  create an index on the varchar field.  wait for a long time.  see attached file.  alternatively create a table with a varchar index, populate it and then attempt a alter table operation.

Suggested fix:
creating varchar indexes should be comparable to creating int indexes in time and obviously the same with altering tables with varchar indexes.
[10 Apr 2008 23:50] David Dick
to replicate bug

Attachment: test_alter_table.pl (text/x-perl), 1.39 KiB.

[10 Apr 2008 23:55] David Dick
sounds very similar to the adding unique indexes bug at 22487
[16 Apr 2008 10:29] Susanne Ebrecht
Many thanks for writing a bug report. We will analyse this.
[17 Apr 2008 15:55] Susanne Ebrecht
Many thanks for writing a bug report.

This is not a bug. But I will look that we will document it.

Let me explain why this is not a bug:

1) it's a hardware question. Usually the hardware needs less time for integer comparison then for character comparison.

Consider here too: int always is just 4 byte at an utf8 varchar you have between 1-4 byte per every sign.

2) in MySQL integer and varchar indexe are implemented different. The integer indexes are uncompressed and the varchar indexes are compressed. Of course MySQL needs here longer during "create index" because of compressing the varchar indexes.

For making your index creation faster, you can try to increase:
myisam_max_sort_file_size (disc space related)
myisam_sort_buffer_size (RAM related)

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_myisam_m...
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_myisam_s...
[21 May 2008 19:16] Paul DuBois
No doc changes made. It's unremarkable that index creation may take different amounts of time for different data types.