Need help with MySQL fulltext indexes

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

Code:

---

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?

PHP Code:

---

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


---

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

Quote:

---

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.

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.

Quote:

---

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.