Newbie question - MySQL index size

Question

I've just started to investigating how I should optimize my database. Indexing seems to be a good idea, so I want to index a VARCHAR column, the engine is MyISAM.

From what I've read, I understand that an index is limited to a size of 1000 bytes. A VARCHAR character is 3 bytes in size. Does this mean that if I want to index a VARCHAR column with 50 rows, I need an index prefix of 6 characters? I came to that number by dividing 1000 with the row number 50, then the bytesize per character that is 3. 1000/50/3=6,66.

It seems a little complicated, so I'm just wondering if I'm thinking right? It seems weird to me that you'd only be able to index 333 rows in a VARCHAR column, using a prefix of 1 character.

Answer 1

I think its meant the varchar width. If the index max size is 1000 bytes and a varchar is 3 bytes long then the max indexable size would be 1000/3.

Answer 2

From what I've read, I understand that an index is limited to a size of 1000 bytes.

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

This means that you cannot index a single UTF8 column more than 333 characters long in a MyISAM table (when calculating the max index size, MySQL assumes 3 bytes per character, though actual length may be much smaller)

You can create as many indexed records as you need.