I need fulltext search in two columns. The columns have fulltext indexes, but the problem is that the query works only with one parameter inside the MATCH function. With two columns, the query returns an empty result.
<?php
$s = "search keyword";
$query = "
SELECT source,title,description,link,pubdate FROM feeds
USE INDEX(PRIMARY)
WHERE (MATCH(title,description) AGAINST(? IN NATURAL LANGUAGE MODE)
LIMIT 40
";
$st = $cn->prepare($query);
$st->bind_param("s",$s);
$st->execute();
$result = $st->get_result();
>Solution :
You need a compound FULLTEXT index on both columns, not a separate FULLTEXT index on each column.
ALTER TABLE `feeds` ADD FULLTEXT INDEX `i_title_description`(`title`, `description`);
Also your SELECT is missing quotes in AGAINST
SELECT * FROM feeds WHERE (MATCH (title, description) AGAINST ('? IN NATURAL LANGUAGE MODE'))