[Postfixadmin-devel] ERROR with UTF-8 mysql tables

[Snaky L. <sna...@go...>]

Hi,

I tried to install postfixadmin into a mysql utf-8 table and came across
this error:

Specified key was too long; max key length is 1000 bytes

As I can see in the sf bugtracker, you already found this, but ATM there
seems to be only a workaround - not to use utf-8 tables.

Also already mentioned was the thread in the mysql bug tracker:
http://bugs.mysql.com/bug.php?id=4541

however I would like to recommend developers of postfixadmin to revisit this
link and scroll down to the very bottom and read the post of [23 Feb 3:34]
Jason Urrich:

I post the text here for your convenience and to have everything in one
place:

------------------------------------
TO ANYONE WHO RUNS INTO THIS PROBLEM
------------------------------------

Before trying to work around this problem, consider what this error is
telling you: you
are trying to set up a key (i.e., a tree searching index) that needs more
than a certain
number of bytes. If you are getting this error for something that you've
marked as a
primary key and your keylength is over 20 bytes (latin1) or 60 bytes (utf8),
then stop
right and go back to your design - this is not a suitable primary key.

If you are running into this problem for a non-primary key or an indexed
column, the
problem comes down to the same thing: you're trying to use an enormously
long string to
do tree index lookup, which means you've probably not understood how keys
are used in
database management systems.

----------------------------------
HOW TO PROPERLY SOLVE THIS PROBLEM
----------------------------------

- if this is for a primary key, redesign your table. It really is this
simple: if you're
trying to do lookups based on very long keys, your table design is bad.
Consider this an
attack on your database designing skills if you must, but search keys should
be short.

- if this is for a non-primary key, redesign your table. Either this is not
a key, and
you should not term it as such, or it is, and it should be a hell of a lot
shorter.

- if this is for an index (INDEX or FULL TEXT), consider what an "index"
means: you are
saying that your entire column should act as a search key. While you may
want to take
advantage of this functionality because it lets you do natural text searches
in your
databases, this is *not* what MySQL is for. If you need full text indexing
for vast data
(say your columns represent full article texts) then you should not be using
MySQL for
this in the first place, but text indexers like Apache's "Lucene" or the
like.

-------------------------------
WHAT MAKES THIS WARNING SO BAD?
-------------------------------

Tree indices let MySQL speed up the process of finding data in your table.
Keys and
index/full text indices speed up the search process by building a separate
data
structure, with the keys ordered in a way that allows quick searching.

These structures are sorted structures, which means that whatever you called
a key or an
index is stored sorted (typically) alphanumerically, regardless of whether
that's how
you've put it in your own table. Being guaranteed sorted helps mysql to very
quickly find
these keys. However, this quickly finding keys is only possible if the keys
are short.

Think of it this way - when you look for a word "cat" in an ordered list of
three letter
words, you're going to first find the place where words start with "c", then
after that,
words with second letter "a", and then words with third letter "t".

This is fast. MySQL will be able to find you the right row(s) of data in
your original
table lightning fast... unless you told it to build an index or to key on
something
huge.

If you're using a key or index of type varchar(255), for instance, you're
telling MySQL
that it may have to do 255 byte comparisons before it can even get to the
list of
matching rows in your original table. This completely destroys mysql's -or
any database
management system for that matter- ability to quickly get you the data you
need. In fact,
it will probably be slower than doing a full table scan of your original
data, because
when you're telling mysql that it needs to do this:

select * from mytable where mykeycolum like 'my huge selection criterium';

where "mykeycolumn" is keyed/indexed, what you're actually telling MySQL to
do is this:

rowids = collapse (select rowids from mytable_mykeycolumn_index where
mykeycolumn like
'my huge selection criterium');
select * form mytable where mykeycolumn in rowids;

-----------------------------------------------
IF YOU THINK YOU NEED MORE BYTES IN KEYS ANYWAY
-----------------------------------------------

It is possible that you're just using MySQL for something simple, and either
don't have
the time or the skill to work out a good table layout to get MySQL to
swiftly do what you
would like. This is entirely possible, and it is understandable that you
might run into
this problem, but consider that this error is not thrown just because the
size limit was
arbitrarily chosen by the developers, and you "just need a few bytes more".

This error is an indicator that you're trying to make MySQL do something
that you really
shouldn't want to make it do. Now is a great time to sit down for half a day
to a day and
learn a bit about how to properly design tables, and what you should and
shouldn't expect
a database management system to do.

-------------
IN CONCLUSION
-------------

Q - Why am I getting this warning?
A - Because you're using a construction intended to speed up the query
process in a way
that will likely slow things down considerably, instead.

Q - How do solve this?
A - Optimise your tables so that you key on short keys, and do full text
searching using
full text indexers (do not rely on a dababase management system to do full
text searching
for you. In order to be efficent, dbms don't know what "languages" are, they
just know
byte sequences and if you're lucky, collation rules).

Q - Will this bug be fixed?
A - Pray it doesn't; if anything, it should be tightened to not allow keys
over 128 bytes
for latin1, or 384 for utf8.

The reason for this is that the "specified key was too long" warning is the
best
indicator that the database designer does not understand the purpose of
table keys and
indices, and needs to smarten up, because he or she's trying to use speed-up
techniques
in a way that'll slow mysql down, instead.