Managing & Using MySQL, 2nd Edition

Full Text Searching

MySQL introduced the ability to search on text elements within a text field in Version 3.23.23 through a special index called a FULLTEXT index. It specifically enables you to do something like:

INSERT INTO Document (url, page_text )
VALUES ('index.html', 'The page contents.');
SELECT url FROM Document WHERE MATCH ( page_text ) AGAINST ('page');

INSERT adds a row to a Document table containing the URL of a web page and its text content. SELECT then looks for the URLs of all documents with the word page embedded in their text.

The Basics

The magic behind full text searching lies in a FULLTEXT index. The CREATE statement for the Document table might look like this:

CREATE TABLE Document (
    url       VARCHAR(255) NOT NULL PRIMARY KEY,
    page_text TEXT         NOT NULL,
    FULLTEXT ( page_text )
);

The FULLTEXT index enables you to search the index using words or phrases that will not match exactly and then weigh the relevance of any matches. As with other indexes, you can create multicolumn FULLTEXT indexes:

CREATE TABLE Document (
    url       VARCHAR(255) NOT NULL PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    page_text TEXT         NOT NULL,
    FULLTEXT ( title, page_text )
);

With this table structure, you can now search for documents that have the word MySQL anywhere in the title or body of the page. You must keep your searches structured against the index, not against the columns. In other words, you can match against title and page_text together with this table, but you cannot look for words that ...