Geek Answers Handbook

MySQL index sizes

I am just starting to explore optimization for my MySQL database. From what I'm reading, indexing seems like a good idea - so I want to create an index on one of the VARCHAR columns in the table using the MyISAM mechanism.

From what I'm reading, I understand that the index is limited to 1000 bytes. The VARCHAR is 3 bytes in size. Does this mean that if I want to index a 50-row VARCHAR column, do I need a 6-character index prefix? (1000 bytes / 50 lines / 3 bytes per character = 6.66)

If so, it seems a little complicated - that’s why I doubt my understanding. It seems rather strange that you could index 333 rows in a VARCHAR column using a 1 character prefix.

Did I miss something?

+7
mysql indexing
source share
2 answers

From what I read, I understand that the index is limited to 1000 bytes.

Index length is limited to 1000 bytes in MyISAM , 767 bytes in InnoDB (per column).

This means that you cannot index one UTF8 column more than 333 characters in the MyISAM table (when calculating the maximum index size, MySQL takes 3 bytes per character, although the actual length can be much less)

You can create as many indexed entries as you need.

+4
source share

I think that meant breadth of breadth. If the maximum index size is 1000 bytes and varchar is 3 bytes, then the maximum index size will be 1000/3.

0
source share

More articles:


All Articles