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=Noption 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=128and 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.
Hi,
just to be sure – the “64/128 indexes max” should probably read “64/128 indexes *per table* max”.
Roland.
LikeLike
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. 🙂
LikeLike
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.
LikeLike
Roland, yes, the mentioned limits are indeed per-table limits.
LikeLike