Getting Started With MySQL's Full-Text Search Capabilities - What is Full-Text Searching? (Page 2 of 6 )
Imagine having a database that contained 10,000 tables. In each of these tables there are 1,000 rows with 100 fields. How would you effectively search this sort of information structure without killing your web server? The answer is MySQL's full-text search capabilities.
A full-text search makes use of indexes, which you can define against a table either when it is created or by using MySQL's ALTER TABLE command. These indexes are setup on specific fields of a table and change the way that MySQL stores the records for that particular table.
For example, let's say that you have a table to store the title, price, description, availability and picture of some computer products. When users perform a search, they will most commonly enter part of the products title or description.
If we setup an index on the title and description fields in our MySQL database, then we can make MySQL store the records for the products table in an indexed format, meaning that when a user performs a search, MySQL can retrieve related records quicker, because it has already indexed (or ordered) them.
Full-text searches are faster than other search methods such as wildcard or character based searches, which are commonly performed using MySQL's LIKE command.
Besides being significantly quicker than normal character based searches, why would you want to use full-text searching? Here's a quick list of points to wet your appetite:
Full-text searching is ideal for extremely large databases that contain thousands or even millions of rows. Computations are performed faster and rows can be ranked based on search relevance, which is returned as a decimal number by MySQL.
Noise words and any words that are 3 characters or less in length such as the, and, etc are removed from the search query. This means that more accurate results are returned. If you searched for "as the people", then the noise words "the" and "as" will automatically be removed from your query.
In addition to simple searches, full-text searches can also be performed in Boolean mode. Boolean mode allows searches based on and/or criteria, such as "+person +Mitchell", which would only return all records that contained the words person AND Mitchell. We will look at Boolean searches later in this article.
The query is case-insensitive, meaning that "cat" is ranked the same as "Cat", "CAT" and "cAT".
As you can see, full-text searching is fast, powerful and smart. It eliminates the need for us to write complicated search and Boolean algorithms, and you can be up and running with MySQL's full-text searching and indexing capabilities in under 5 minutes.
Your First Full-Text Search Let's jump right in and see full-text searching in action. First off we need to create our database, so fire up the MySQL console window and create a new database called testDB, like this:
create database testDB: use testDB;
Next, we need to create a table using MySQL's FULLTEXT command, specifying which fields we want to index for searching:
create table testTable ( pk_tId int auto_increment not null, firstName varchar(20), lastName varchar(20), age int, details text, primary key(pk_tId), unique id(pk_tId), fulltext(firstName, lastName, details) );
What we've done here is create a basic table that will store the details of some people. The first field is a uniquely identified primary key and the rest are simple varchar, int and text fields.
If you already have a table setup and want to index existing fields, use the ALTER TABLE command like this: ALTER TABLE myTable ADD FULLTEXT(field1, field2);
Take a look at the highlighted line in our CREATE TABLE command, shown above:
fulltext(firstName, lastName, details)
This line tells MySQL to set an index on the firstName, lastName and details fields of our table. Indexes can only be created on fields of type VARCHAR and TEXT. Because these fields contain indexes, we can now use the power of a MySQL full-text search to find records in this table based on the values in these 3 fields.
Before we can perform a full-text search however, we need to add some records to our table with the following MySQL commands:
insert into testTable values(0, 'Mitchell', 'Harper', 20, 'Mitchell is the founder and manager of devArticles and various other sites across the SiteCubed network');
insert into testTable values(0, 'Ben', 'Rowe', 19, 'Ben is our Flash guru. He is currently writing a series of Flash articles that show beginners how to use Flash to create dynamic content with PHP');
insert into testTable values(0, 'Havard', 'Lindset', 19, 'Havard is a PHP and MySQL power user');
insert into testTable values(0, 'Michael', 'Manatissian', 25, 'Michael is the senior devArticles editor and is a capable Linux/Apache administrator');
insert into testTable values(0, 'Sandra', 'Lee', 23, 'Sandra is our finance and accounting wizard, having 4 years of experience under her belt');
Because full-text searching was designed for larger databases, it is possible for MySQL to return incorrect results when it's used on tables containing smaller amount records, that's why I've created 5 "chunky" records for our test table.
Now that we have records in our table, how do we search them? Simple -– we create a query to invoke MySQL's full-text search feature, like this:
select firstName from testTable where match(firstName, lastName, details) against('guru');