SQL: Ideal engine type (MyISAM vs InnoDB) and data type for unique text column

Hi I have a php website using mysql and I have a table with a column called 'Name'. I intend for it to have the following features:

• It should be a varchar(N) type just like how regular names are.
• It may be long, but should never contain so-called "descriptions" as that is in another field that I don't care about searching. (maybe in the future, which I might even just put it in another table)
• It MUST be unique and searchable, which seems to make it a suitable candidate as a primary key.
• Searches are the simple types, just behaviours like the mysql LIKE %keyword% will do.
• This table is (very) frequently read, new rows inserted every once in a while, rows removed/updated very rarely.
• Many other tables refer to values on this table, which ideally I want to have foreign key constraints on the other tables which leads me to want to use InnoDB.

My question is, should I use MyISAM or InnoDB for this table? Also is it ok for my not so long varchar to be used as a primary key considering the frequency of read/amount of memory used/amount of warnings on the internet against varchar primary keys?

But I would really want to benefit from the foriegn-key constraints that InnoDB offers or should I just worry about开发者_高级运维 it at the php level?

My concern, in particular, is MyISAM's Full-text search capabilities. I tried to read the official mysql webpages to understand what is it for, but failed to understand enough to judge if my situation will benefit from it.

---

Short answer: InnoDB with surrogate primary key.

Longer Answer

Since you intend for the table with the Name column to have many child tables, I'd recommend a surrogate key using an INT UNSIGNED (or even BIGINT UNSIGNED if your data warrants that). That way all your child tables aren't required to have a Name column in them, saving space.

In InnoDB, short primary keys are the best option, because the primary key is included in all secondary indexes: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

FULLTEXT indexes are not required to do simple LIKE('%keyword%') matching. They help if you're interested in natural language matching, which you did not indicate as a requirement.