How to determine the index size

[builder]

Hi,

I must search on a tinytext field. I want to put an index on it to make it faster. but i have to specify the size for that index.

MySql says this: "....Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters...."

Ok so how do i calculate the number of bytes needed to get a good index on a tinytext field knowing that smaller is better? My Character set is in UTF-8.

Tanks

[longneck]

are you sure you want a standard index? with a standard index you would only be able to search for left-anchored text.

maybe you really want a fulltext index. see http://www.sitepoint.com/forums/showthread.php?t=540471

[builder]

Yes, well i want an exact match of a given string...

Please let me know!

Thanks

[longneck]

how long will most of the strings be?

[builder]

The very max would be 50 characters.

It's the part of an URL that i stored in DB. Most of them are between 20-30 characters long.

Thanks

[longneck]

if that's true, then tinytext is a poor choice of data type. use varchar instead.

[builder]

Ok, tinytext was not the best choice. But still, if you are not sure about the maxlenght, how do you determine the index size that is needed knowing that smaller is better?

Thank you for taking the time to answer me.

[longneck]

in a perfect world, you would want the smallest index prefix size that results in the highest cardinality. sometimes your index gets too big to be useful (i.e., it keeps pushing other indexes out of memory) so reducing your index prefix will help. i don't have any good rule-of-thumb for this particular situation, but the main factor i take in to consideration is the amount of memory the server has available, the amount of data the server will store in the foreseeable future, and how critical it is that queries on this table be efficient.