Recently, I was populating a database with lines from a number of log files. One of the key pieces of information in each of these log lines was a URL. Because URLs can be pretty much as long as they want to be (or can they?) I decided to make the URL field a Text type in my schema. Then, because I wanted fast lookups, I tried to add an index (key) on this field and ran into this guy:
ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length
It turns out that MySQL can only index the first N characters of a Blob or Text field – but for a URL, that’s not good enough. After talking it over with my team members, we decided to instead add a field – url_md5. By storing the md5sum of each URL, we could index on the hash field and have both fast lookups and avoid worrying about domains like this fitting into a VARCHAR.