Search code examples
mysqlindexingfull-text-searchfull-text-indexingfulltext-index

FULLTEXT index on one column vs multiple columns in following scenario?


I have this table structure and I am using MYSQL

post` (
  `post_id_pk` INT,
  `title` VARCHAR(100),
  `description` TEXT,
  `search_content` TEXT
)

I have a search functionality. IF an user type a text into textbox and if that word contains in either title column or description column's records those corresponding posts should display to the user. I use this query to get the result

MATCH (search_content) AGAINST (@searchQuery IN NATURAL LANGUAGE MODE)

This is working fine. What I want to know is Did you see the "search_content" column? What I did is when user submit an article I concat both title, description data and put whole text into "search_content" column. Then I indexed only this column for the full text rather than combining title and description column to make the fulltext index. So performance wise what is the best?

1) Merge two columns to FULLTEXT INDEX and use them

2) Use one column for FULLTEXT ( This column should include all data from both two columns )


Solution

  • You won't gain any performance increase by merging 2 columns data to one. Just go ahead and use what MYSQL says. You can add fulltext index on two columns easily. Just use them.