Creating the fulltext key:

CREATE TABLE sites (id int(11) auto_increment, county char(255), city char(100) descshort char(255), desclong text, location char(255), directions text, addinfo text, adddate date, chgdate date,type char(30) , PRIMARY KEY (id), FULLTEXT KEY ft(county,city,descshort,desclong,location,directions))

Adding the fulltext key:

ALTER TABLE sites ADD fulltext ft( county,city,descshort,desclong,location,directions)

Display a table's fulltext key

mysql> show index from sites;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
| sites | 0 | PRIMARY | 1 | id | A | 123 | NULL | NULL | |
| sites | 1 | ft | 1 | county | A | NULL | NULL | NULL | FULLTEXT |
| sites | 1 | ft | 2 | city | A | NULL | NULL | NULL | FULLTEXT |
| sites | 1 | ft | 3 | descshort | A | NULL | NULL | NULL | FULLTEXT |
| sites | 1 | ft | 4 | desclong | A | NULL | 1 | NULL | FULLTEXT |
| sites | 1 | ft | 5 | location | A | NULL | NULL | NULL | FULLTEXT |
| sites | 1 | ft | 6 | directions | A | NULL | 1 | NULL | FULLTEXT |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+
7 rows in set (0.00 sec)

Add a fulltext search to a select statement:

 

More MySQL