Future proofing FULLTEXT search..

Question

I have a FULLTEXT index called cart_product_fti on a table

Whenever I use it, I have to list all the fields that are in the index exactly:

MATCH (foo,bar,etc)

My thoughts are, if later I decide to add/remove a field to the index, all the scripts are going to have to be updated or they will be broken.

So I am wondering if there is a way to get a list of all the fields in the index and then insert the list into my MATCH() so it won't have to be updated if a change is made to the FULLTEXT index?

Answer 1

Possible solutions:

• Just do the work. A properly structured project probably has just 1 or 2 classes or files with the specific query / datarelation, so it shouldn't be that much work.

• Keep on querying information_schema.statistics again and again for every query to build a query dynamically (hardly efficient).

• Define a stored procedure which searches in the table, in which case altering the table / fulltext index layout would only require you to alter the procedure once.

Edit: for some reason key_column_usage doesn't seem to hold a FULLTEXT reference, and we have to resort to the information_schema.statistics table?

Answer 2

To list all the fields in the index, you can use the SQL command SHOW INDEXES FROM <table>.

It will list each index in the table, and for indexes with several columns, you will have one entry for each column. You should only use that solution with proper caching of the column list for the index.

Another solution in your case would be to store the list in a configuration file, and to make sure you update the configuration if the database structure changes.