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.

IIRC, full text indexes can only be on MyISAM tables. What is the engine type for the table POSTADS? — OMG Ponies
– OMG Ponies, Commented Jun 24, 2010 at 20:37

Pete · Accepted Answer · 2010-06-24 21:35:51Z

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`
);

answered Jun 24, 2010 at 21:35

Pete

1,7839 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Fulltext Search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related