Tis a gift to be simple

I was just reading up on the syntax for index hints in MySQL, and noticed this:

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs. I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

mysql> create table t(a int primary key);
mysql> select * from t force index(PRIMAR);
Empty set (0.00 sec)

mysql> alter table t add key PRIMARY_2(a);

mysql> select * from t force index(PRIMAR);
ERROR 1176 (HY000): Key 'PRIMAR' doesn't exist in table 't'

I actually considered adding support for prefixes of command-line options to Maatkit, once upon a time. This way you’d be able to say --rep instead of spelling out the full option name; some of the options are very long-winded. This is pretty standard behavior, and even the MySQL command-line tools let you do it. But I came to my senses quickly when I realized that this would never let us rest easy about backwards and forwards compatibility. Even if it weren’t a potential problem, I think there are more important things to work on in Maatkit.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.