I'm looking to run a query that will return a list of columns in a table that are FULLTEXT indexed. The table is in MyISAM format and i'll be using php to construct the query.
Ideally i would run the query and it would return the information so i could construct a comma separated string of the columns.
e.g. "first_name, last_name, email"
Is this possible in MySQL?
You can get that information from the information_schema.STATISTICS table.
I'll give you the query to get all columns in the table that are in one or more FULLTEXT indexes, since I think that's what you are asking for. Bear in mind that the specific combinations of columns in each FULLTEXT index are very important. MySQL can't use a FULLTEXT index to search multiple columns unless there is a single FULLTEXT index that includes all of those column.
Here's the first query that gives the output you asked for:
select group_concat(distinct column_name)
from information_schema.STATISTICS
where table_schema = 'your_db'
and table_name = 'your_table'
and index_type = 'FULLTEXT';
And here's one that shows the various combinations of columns in FULLTEXT indexe if there is more than 1 on the table:
select index_name, group_concat(column_name) as columns
from information_Schema.STATISTICS
where table_schema = 'your_db'
and table_name = 'your_table'
and index_type = 'FULLTEXT'
group by index_name;