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