Conceptual overview

Under traditional relational database systems, you are limited to using a LIKE or MATCHES condition when you search for words or phrases in a column that contains text data. For example, if you want to return all rows of the table videos in which the VARCHAR description column contains the phrase multimedia text editor, you are limited to executing a statement such as:

SELECT * FROM videos 
WHERE description LIKE '%multimedia text editor%';

Although this SELECT statement returns a correct list of rows, it is probably slow, depending on the size of the table and the amount of text data in the description column. Because a traditional secondary index on the column description is not useful in this type of search, the whole table has to be scanned.

In addition to being slow, the SQL statement only retrieves rows in which data has been entered exactly as it appears in the LIKE clause. Rows in the table that contain the word multimedia consistently misspelled as mulitmedia, for example, are not returned, although they are probably of interest to you.

If you want to also find documents that contain synonyms or alternate spellings of the words you are searching for, you must construct a complicated statement that contains many ORs in the WHERE clause, such as:

SELECT * FROM video
WHERE description LIKE '%multimedia text editor' OR
description LIKE '%multi-media text editor%' OR
description LIKE '%multimedia document editor%' OR
description LIKE '%multi-media document editor%';

This type of SELECT statement is often slow. Traditional relational database systems cannot perform sophisticated and fast searches of this type.