Steps to implement mySQL FULLTEXT

mySQL has full text indexing and searching support from version 5.0 onwards however there are some limitations. If you need to implement full text indexing then please review points given below:

1. mySQL allows full text indexing on MyISAM tables and not any other table type. If you try to create a full text index on an InnoDB table for example you will receive the following error message

Error Code : 1214
The used table type doesn't support FULLTEXT indexes

If you plan on using a table with InnoDB for full text indexing then you will first need to change it to MyISAM. Run the following statement to change the table to MyISAM

alter table <table name> engine = MyISAM;

2. You can only use the full text index on CHAR, VARCHAR and TEXT data type. If you try to create it on a column with a data type other than he ones specified then you will receive the following error message

Error Code : 1283
Column '<column name>' cannot be part of FULLTEXT index

3. You can specify the full text index in the CREATE table statement or ALTER table statement. Example of specifying full text index in the CREATE table statement is as follows

CREATE TABLE product
(
pid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
pname VARCHAR(200),
pcode VARCHAR(20),
pdesc TEXT,
FULLTEXT (pname,pdesc)
) ENGINE=MyISAM;

Example of specifying full text index in the ALTER table statement is as follows

ALTER TABLE product ADD FULLTEXT(pname, pdesc);

After completing the above steps you can test whether the full text index is working or not by inserting some data into the table. Please note you should enter at least 10 or more records and the same data should not be copied in each record otherwise it will not work.

The following statement will try to return result using the MATCH()..AGAINST syntax which is how full text index searches through the records in the table.

SELECT * from product where MATCH(pname, pdesc) AGAINST('test');

Please note that full text indexing will only run on the columns in which the full text index has been created. If you try to run the above statement on a non full text index column then it will not run.

If we take the above table for example we have not created full text index on the “pcode” field. If we run our SELECT statement as

SELECT * from product where MATCH(pname, pcode) AGAINST('test');

we will receive the following error

Error Code : 1191
Can't find FULLTEXT index matching the column list

Hope the above helped. Let me know if you have any questions/comments.

Leave a Reply

Your email address will not be published.