Mysql Fulltext Search

Question

This query is running only for data available in t1.ads_category, not for the other two columns:

SELECT t1.id,
       t1.ads_type,
       t1.ads_category,
       t1.ads_subcat,
       t1.ads_title,
       t1.ads_title,
       t1.ads_description,
       t1.ads_city,
       t1.ads_location,
       t2.ads_image,
       t2.ads_activate,
       t2.postads_id 
  FROM postads t1 
  JOIN nextpostads t2 ON t1.id = t2.postads_id 
 WHERE MATCH(t1.ads_category,
             t1.ads_title, 
             t1.ads_description) AGAINST ('".$search_text."') 
   AND t1.ads_city='".strtolower($city_name_url[0])."' 
   AND  t1.ads_location='".$location_val."' 
   AND t2.ads_activate='Yes'

What is wrong with this ? Is there any problem with FULLTEXT SEARCH.I m seeing Cardinality is 23 in FULLTEXT INDEX. I m new to FULLTEXT.

Answer 1

Repost of my answer to the repost ;-)

You need to create a full text index on the combined fields ads_category, ads_title, and ads_description, not on each individual column.

E.g.:

ALTER TABLE `postads` ADD FULLTEXT (
`ads_category` ,
`ads_title`,
`ads_description`
);