Professional LAMP: Linux®, Apache, MySQL®, and PHP5 Web Development

4.3. Full-Text Searching

The owners of the car lot have been impressed so far with your ability to query exactly what they need, give the various listings to help increase the productivity of their sales staff—but they still want more. What they'd like now is a way to search through the car inventories by keyword. Their search needs aren't too complex, but a simple LIKE comparison in a WHERE clause just isn't going to get the job done.

What you need just happens to be built into MySQL—full-text searching. Full-text search, which is enabled by default in MySQL, allows you to perform a natural language search on any text column you specify. You can't just go out and start full-text searching at will in your database, though—you have to do some preparation first.

4.3.1. Enabling Full-Text Searching

Enabling full text searching in MySQL involves simply creating an index on a column or columns for a table. You can do this one of two ways: when the table is created, as part of the CREATE TABLE command, or later on in the life of the table.

4.3.1.1. Creating a New Table with Full-Text Search Enabled

The first full-text index you're going to create will be part of a brand new table. To specify a full-text index at table-creation time, all you have to do is specify a full-text index and the list of indexed columns at the end of the column specifications:

CREATE TABLE tablename (column_specs, FULLTEXT (columns_to_index) )

You can specify a single column in the FULLTEXT column list, or ...