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

    just to be sure – the “64/128 indexes max” should probably read “64/128 indexes *per table* max”.

    Roland.

    Like

  2. Maarten says:

    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

  3. Frank says:

    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

  4. sergey says:

    Roland, yes, the mentioned limits are indeed per-table limits.

    Like

Leave a Comment

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 )

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