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:Code:CREATE TABLE messages ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, subject VARCHAR(100) DEFAULT NULL, message TEXT, 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?