Need help with MySQL fulltext indexes

[Kennel]

I'll start out with my table structure (simplified):

CREATE TABLE messages (
   id        MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
   subject   VARCHAR(100) DEFAULT NULL,
   message   TEXT,
   PRIMARY KEY( id ),
);

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:

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?

[weirdbeardmt]

SELECT * from table_name where col_name like '%searchtext%'; 

[MattR]

That SQL won't/shouldn't use the full-text index. You have to write special queries:
http://www.mysql.com/doc/F/u/Fulltext_Search.html

[Kennel]

Originally posted by MattR
That SQL won't/shouldn't use the full-text index. You have to write special queries:
http://www.mysql.com/doc/F/u/Fulltext_Search.html

Yeah, I had a go at it and it works. My query is as follows:

SELECT subject FROM messages WHERE MATCH (subject,message) AGAINST ( 'searchword' );

I've tried searching with wildcards (%searchword%) but it doesn't seem to work. Is it impossible to use wildcards in freetext index searches?

I still haven't figured out if I added the correct indexes. It feels kinda odd to add indexes to both columns and then a third index to "both columns". I'm not sure this is required. I reccon this will add to the size of the db quite considerably.

[Robo]

The %searchword% method doesn't use indexes at all, it does not provide you with any of the features FULLTEXT uses. The wild card is just a very basic method of searching a database.

You probably only need to create one index, FULLTEXT(subject, message)

Either when creating a new table, or use ALTER TABLE.

[Kennel]

Originally posted by Robo

You probably only need to create one index, FULLTEXT(subject, message)

Either when creating a new table, or use ALTER TABLE.

Yeah, I tried that, but it doesn't seem to work when I want to search in only subject or message. With an index for both I have to search in both.

SELECT subject FROM messages WHERE MATCH (subject) AGAINST ( 'searchword' );

gives me an error because there is no index for "just" subject.