Need help with MySQL fulltext indexes
I'll start out with my table structure (simplified):
I'm designing a simple forum and I want to add a search feature. I read about MySQL fulltext indexes and it sounded just like the thing I needed. I want users to be able to search in subjects, messages and both subjects and messages. I am, however, not sure how I would set up the indexes needed to do such a search. This is what I have come up with so far:
CREATE TABLE messages (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
subject VARCHAR(100) DEFAULT NULL,
PRIMARY KEY( id ),
FULLTEXT KEY subject (subject),
FULLTEXT KEY message (message),
FULLTEXT KEY both (subject,message)
I believe I have to add indexes like above. The main question. How would I then search this messages table?
BTW. Can I use wildcard searches in fulltext indexed fields?