MySQL Bugs: #35980: CREATE INDEX on a varchar field on a large table takes a very long time

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

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.

to replicate bug

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

sounds very similar to the adding unique indexes bug at 22487

Many thanks for writing a bug report. We will analyse this.

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...

No doc changes made. It's unremarkable that index creation may take different amounts of time for different data types.