the ability to search on text elements
within a text field in Version 3.23.23 through a special index called
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
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 magic behind full text searching lies in a
FULLTEXT index. The
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 ) );
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
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
page_text together with this table, but you cannot look for words that ...