What a good index length for a MySQL text column?

Question

I have a TEXT column in a MySQL DB. The app is like a blog and the col contains the body of the "posts." I will be doing a lot of complex boolean searches on this column.

The MySQL docs say you need to set the index length for TEXT columns with a max length of 767 bytes for InnoDB tables.

Should I just set it to the maximum length? Is it better to make it less than that?

Answer 1

It depends what you will have in it.

I suspect from your question that you are imagining that an index on a column can do things that it cannot. A conventional index is not a full-text index and can't be used to find words, or any substring match other than a prefix. Database indexes are used to find an exact value, or a range of values only - they can't perform arbitrary matching.

Assuming you were going to use an index for something it can do, then the length should be the shortest length that lets you have sufficient specificity that you can find the rows you want without reading too many of them. How long that is depends on the contents of your table, for example if your table has many rows with the same prefix, the index needs to be longer than that similar prefix to avoid putting them into the same index entry - meaning that they all need to be searched.

Answer 2

If you're doing 'complex boolean searches', there's every likelihood that a normal index isn't going to be any use to you anyway. I think you'll probably find you need a full-text index.