SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow 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?

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT from table_name where col_name like '%searchtext%'
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by Robo; Mar 4, 2002 at 17:23.
    Work smarter, not harder. -Scrooge McDuck

  6. #6
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •