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.