Show a tables FULLTEXT indexed columns

Question

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?

Domenech

Answer 1

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;

Snailpaced

Answer 2

Here's another way:

SHOW CREATE TABLE [database_name].[table_name]

Replace the bracketed placeholders with your own values.

Peruse the output for FULLTEXT lines.

Peafowl

Answer 3

I am too late but here it is.

In case you don't remember the table names which you've indexed to, you can get their table and schema names from the information_schema database.

All you need to do is -

select table_name from information_schema.STATISTICS  where table_schema = 'your_schema_name'  and index_type = 'FULLTEXT';

The above code will give you only the names of all tables which contains full-text indexing.

select table_name,table_schema from information_schema.STATISTICS  where index_type = 'FULLTEXT';

However, this will give you database name as well as the table name which have full text indexing

Hope this helps!

Aright

Answer 4

I'm just going to drop what worked for me.

SHOW CREATE TABLE `db_name`.`table_name`;

Ensure that the db_name and table_name are in separate backticks joined together by a dot(.).

Odd