Say I've got a field, defined as maybe varchar(50). Should I have a regular index on this thing so that ORDER BY select statements are optimized, or is an inverted metamorph index going to do just about as good a job? Thanks!
It depends on the entire query. If you're doing something like "SELECT ... WHERE fld1 LIKE $query ORDER BY fld2", a Metamorph compound index on (fld1,fld2) would help. If you're just doing "SELECT ... ORDER BY fld2" with no WHERE, then a regular index on fld2 helps.
However, note that the compound (2nd and later) fields in a Metamorph index are stored with a fixed size, and should be small (int/date/counter) for best performance. A varchar field would be stored with the size it was declared with in the table create statement. This means that a compound index on a varchar(50) field may truncate field values that are larger (only in the index, not the table), causing post-processing. It also means the index would be rather larger, since it adds 50 bytes per row. If you know the data is mostly say 5 to 10 char values, it may be useful to re-make the table with the field as varchar(10) if you're going to make a compound index on it. Won't affect anything else.