On possibility to have more than 64 indexes

I was asked on Sun Tech Days whether it is possible to use more than 64 indexes in MySQL. I’ve asked my colleague, Ingo Strüwing, and found out the following: yes, it is possible to have more than 64 indexes, and no additional patches are required. You can get 5.0 source tarball, ./configure it with --with-max-indexes=128 and you’ll get a server that can use up to 128 indexes. One needs to be cautious though:

  • --with-max-indexes=N option looks like you could configure server with arbitrary maximum number of indexes. That’s not true, if you use N>128 then the server will compile but it will not even be able to start.
  • 128-indexes build of server is not widely used, so potentially it can be less stable than the regular build. My experience: I compiled current 5.0 source with --with-max-indexes=128 and it has passed all bundled tests (good) but I don’t see any tests that would use more than 64 indexes (bad). I’d recommend to do some testing before you roll out anything of value on 128-indexes build.
  • Work on 128-indexes version is documented here: BUG#10932.

Hope this information was helpful.

4 Comments

  1. My guess is (haven’t seen such a table yet), that if you are close to getting to 64 indexes, you’d better change some things in your table structure. I can’t think of a table with normalized data where I’d need such a vast amount of indexes. 🙂

    Like

  2. I agree with Maarten, someone should really have a darn good reason to justify putting 64 or more than 64 indexes. This just smells bad data modeling.

    Like

Leave a Reply to Frank Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s