I try to find out the 'optimal' index size for the title column. The title field is UNIQUE, but I'm wasting lots of space when using an unique index or even the field title as a primary key.
Is there any formula to calculate the optimum index length for such a field? The content itself doesn't change and I know that the average length is 25 characters. This is the reason I made the index 25 characters long.
Another solution would be to make the index just 5 characters long, having to scan a couple hundreds of rows to find out the corresponding id for a given title.
A total different thing came also into my mind: delete the title index and create the MD5 value for title and store the first (or last) 4-6 bytes. Todays CPUs are fast enough to do this, and I would save some I/Os and disk space.
Yes, I know how to select and compare, but who tells me what the 'optimum' index size is. Right now it is title(25), your example is title(10), another extreme is title(255) or unique and on the lower end is title(4).
I'm just wondering if there is a magic formula to determine the right index length :-) (... since it is almost impossible to test this - the creation of each database takes hours...)