(I have a feeling I'm missing something here ... )

I would have thought they're pretty self explanatory?

KEY is your basic column index. It's a quick reference to values stored in rows for the column the index is applied to. Indexes speed up SELECTS by allowing MySQL to scan the index for matches rows rather than having to load and scan the entire table which it would do in the case of no [applicable] indexes.

UNIQUE as it's name implies requires rows to contain unique values for the indexed column. As far as I'm aware, it alone cannot aid MYSQL in optimising SELECT statements like a KEY index can.

PRIMARY KEY is both UNIQUE and INDEX.

FULLTEXT indexes individual words in a CHAR, VARCHAR or TEXT column.

There isn't much more to it than that.

Unless you mean the storage methods for indexes. Which can be HASH (only available to MEMORY / HEAP tables), BTREE or RTREE, but they're just the file format that the index is stored in. Never bothered to find out if there is any advantage of using one over the other.

From a general programming perspective:

An R-tree is a data structure for indexing rectangles, which I guess would tally with Matt's comment about a having key which represents spatial data, ie. a point, line or area.

A B-tree is a similar but much simpler construct for indexing scalar values.

Your summary sounds reasonable though. Hash lookup efficiency is very much dependant on the suitability of the hash function with respect to your dataset. If your data clusters around a few hash values rather than being spread out over many then you are likely to get collisions where two or more key values generate the same hash value. This would be the point where your performance plummets as you can't go directly from the hashed value to the record you desire because you have to then search through all the records with that hash. I guess this is the point you have to optimise your table.

B/R-Trees are not subject to such collision problems but require more work when constructing the index (ie adding/removing records) because they optimise as they go along: Trees are most efficient when they are balanced - ie. at each decision point when traversing the tree there are roughly equal numbers of records in all directions. Most decent tree implementations are self-balancing to a degree, and this is where most of the work needs to be done when adding/removing records. However, it is usually possible to design a pathological dataset that would result in a severely unbalanced tree and if your dataset were unfortunate enough to conform to this then you would need to regenerate the tree to regain efficiency (this process would be largely analogous to optimising your hash table).

In summary, your summary would sound plausible :)