CODEWITHSUNDEEP
mysqldatabaseindexing
Dmitriy Likhten
Dmitriy Likhten

Reputation: 5216

MySQL breaking the 1024 byte index limitation

Is there a way to get around the 1024 byte key size limitation in MySQL? Basically this is causing havoc when trying to do a compound index.

Upvotes: 3

Views: 298

Answers (2)

James Anderson
James Anderson

Reputation: 27478

You could hash you values using crc32, MD5 or similar, then index the hash.

You would need to add an extra compare against the original field in your SQL to deal with collisions but that wont cost you very much as collisions are extremely rare.

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881683

While I hesitate to suggest that you're doing something wrong (since I don't have the full details), it's rather unusual for a single key (even a multi-part key) to be that big. I've seen tables where the aggregate of all keys on a table can be large, but rarely a single key.

The whole point of keys is to use a relatively small part of the row to quickly locate that row. If you're getting to the point where your keys are sizable, you probably need to step back and re-examine why you think you need them that large.

Your best bet would be to post the schema here so we can suggest alternatives.

If you really do need keys as large as that, you may find that your only alternative is to switch to another DBMS. For example, DB2/z has an 8K limit for each key. I'm not suggesting you try to purchase a mainframe here, it's just that's the only DBMS I know the limits for off the top of my head - obviously you should look at a DBMS that's not going to cost a couple of million dollars to implement :-)

Upvotes: 3

Related Questions