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