Monday, December 29, 2008

Mysql increase key length

We have recently been converting most of our databases from latin to UTF-8 and have encountered issues with smaller Mysql default keylength. Mysql by default allows a max key length of 1024 size. While it was quite a bit of work to change the current data to confirm to the key length, the easier way was to recompile the Mysql source with a custom key length. Here I will detail the steps to do that:

tar zxf mysql-5.0.67-percona-b7-src.tar.gz

Now you'll want to make the edits to the myisam.h file to use our longer key length instead of the default 1000 byte keylength. Below is a compare of the file before and after change:

--- /tmp/myisam.h 2008-05-28 13:36:22.000000000 -0700
+++ include/myisam.h 2007-03-15 14:15:41.000000000 -0700
@@ -48,12 +49,12 @@
#define MI_MAX_KEY MAX_INDEXES /* Max allowed keys */

-#define MI_MAX_POSSIBLE_KEY_BUFF (1024+6+6) /* For myisam_chk */
+#define MI_MAX_POSSIBLE_KEY_BUFF (4096+6+6) /* For myisam_chk */
The following defines can be increased if necessary.
But beware the dependency of MI_MAX_POSSIBLE_KEY_BUFF and MI_MAX_KEY_LENGTH.
-#define MI_MAX_KEY_LENGTH 1000 /* Max length in bytes */
+#define MI_MAX_KEY_LENGTH 4000 /* Max length in bytes */
#define MI_MAX_KEY_SEG 16 /* Max segments for key */


Next you'll need to run configure / make install

./configure --prefix={choose a name here like /tmp/mysql_mod}
sudo make install

No comments:

Post a Comment