1

I created a FULLTEXT index on a table in a MySql database, and left out a column when I did so. I have been trying to figure out how to add this column so that it can be searched against along with the other columns in this table that have already been indexed.

I have searched SO and the web, but am not getting anywhere. I tried going into phpMyAdmin, and when you click on the table and then look at the structure, all the way on the right there is a dropdown that says "More." This has a "Fulltext" option that seems to add fulltext indexing to the column, so I did this, but it is still not allowing me to search the column.

Did I add FULLTEXT to this column? Either way, how do I get this to work?

Rod the Sun God
  • 57
  • 1
  • 8
  • 1
    Drop the index first and then re-create it with all columns? – Eric J. Apr 27 '15 at 23:28
  • I need the name of the Fulltext Index column in order to do that, right? But it it doesn't show up in phpMyAdmin. How do I find it? I tried Googling this earlier today but couldn't find anything helpful. Found this: (http://stackoverflow.com/questions/16280918/how-to-find-full-text-indexing-on-database-in-sql-server-2008), but it is not for MySQL. – Rod the Sun God Apr 28 '15 at 04:11

2 Answers2

8

For SQL Server users, it can be done like this:

ALTER FULLTEXT INDEX ON [table_name] ADD ([column_name])
Farooq Hanif
  • 1,779
  • 1
  • 15
  • 22
2
ALTER TABLE foo DROP FULLTEXT old_ft_index_name, ADD FULLTEXT(this, that);
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • He's using phpMyAdmin, so not sure whether he can directly execute this or not. – Eric J. Apr 28 '15 at 04:15
  • I do have the ability to run SQL queries directly, but as I asked above, how do I find the name of the existing Fulltext Index? – Rod the Sun God Apr 28 '15 at 15:39
  • `SHOW CREATE TABLE foo;` – Rick James Apr 28 '15 at 15:46
  • Thanks! I had done that but it wouldn't all display. Didn't realize there was another button to show everything ("Print view (with full texts)"). Now I can see the title, etc. For the record, what are the 'this' and 'that' arguments supposed to be? Is this where I list the columns to be indexed? – Rod the Sun God Apr 28 '15 at 17:12
  • Apparently, I was creating a Fulltext Index of the one column when I selected the button I mentioned, but it was not part of the same Index as the others. This was what worked, for the sake of anyone else with a similar problem: ALTER TABLE `foo` DROP INDEX `bar1`, DROP INDEX `bar2`, DROP INDEX `bar3`, ADD FULLTEXT INDEX (`baz1`, `baz2`, `baz3`, `baz4`,`etc`) – Rod the Sun God Apr 28 '15 at 17:36