MATCH ... LIKE returns nothing

Question

I'm trying to create my own blog in CodeIgniter.
One key feature of a blog, is that you can search for posts by using some keywords. These keywords must be found in the title, content, category and/or keywords.

I'm still working on my database (e.g. extracting category and keywords into seperate tables), but this is what I've got so far

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` varchar(10000) NOT NULL,
  `date` date NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `titel` (`title`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`),
  FULLTEXT KEY `keywords` (`keywords`),
  FULLTEXT KEY `category` (`category`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

I'm not sure about the content column, probably should use text instead of varchar(10000)

In my table, I have 2 posts

ID | TITLE                        | CONTENT          | DATA       | KEYWORDS       | CATEGORY
---+------------------------------+------------------+------------+----------------+----------------------
 1 | Welcome to my website        | A few paragraphs | 2014-02-09 | Welcome        | Site
 2 | All Windows Keyboard layouts | A few paragraphs | 2014-03-11 | A few keywords | Windows Deployment

My query is as follow, I tried to keep is as simple as possible for now

SELECT * 
    FROM `posts`
    WHERE MATCH(`title`) AGAINST('Welcome');

I can't seem to figure out why phpMyAdmin doesn't return a thing.

I also tried a more complex query to see if it returns anything, without any luck

SELECT *, MATCH(`title`) AGAINST('Welcome') AS Relevance
    FROM `posts`
    WHERE MATCH(`title`) AGAINST('Welcome')
    ORDER BY `Relevance` DESC

If I understand correctly, MATCH x AGAINST y can be with multiple keywords.
That's what I'm looking for. I want to be able to add a couple of keywords to the query and find the posts that score highest relevance in title, content, keywords and category

@zerkms Then I get the complete table (because of the `SELECT *`) and the table Relevance contains 0 in both rows — BlueCacti, Mar 25 '14 at 21:54

score 2 · Answer 1 · edited May 23 '17 at 10:26

2

Apparently 'welcome' is in MySQL's default stopword list.

You can search for something else for testing purposes, and you can change the stopword list if you think it wont work in your case.

Also, check out this question.

edited May 23 '17 at 10:26

Community

1
1

answered Mar 25 '14 at 22:05

Vatev

7,493
1
32
39

score 0 · Answer 2 · answered Mar 25 '14 at 22:13

0

This is what you want:

SELECT *, MATCH(`title`) AGAINST('website' IN BOOLEAN MODE) AS Relevance
FROM `posts`
WHERE `title` LIKE '%website%'
ORDER BY `Relevance` DESC

answered Mar 25 '14 at 22:13

echo_Me · Accepted Answer · 2014-03-25T22:20:20.570

-1

Try this:

 SELECT *
 FROM `posts`
 WHERE MATCH(`title`) AGAINST('website' IN BOOLEAN MODE);

DEMO HERE

Welcome looks like a stopword in mysql , you have to use LIKE statment for it.

  SELECT *
  FROM `posts`
  WHERE MATCH(`title`) AGAINST('website' IN BOOLEAN MODE)
  OR    title LIKE '%Welcome%'

edited Mar 25 '14 at 22:20

answered Mar 25 '14 at 21:52

echo_Me

37,078
5
58
78

As far as I understand how `MATCH x AGAINST y` works, you can add multiple keywords, that's what I'm looking for. Otherwise I would indeed use `LIKE` – BlueCacti Mar 25 '14 at 21:55
What is the difference in using `IN BOOLEAN MODE` ? – BlueCacti Mar 26 '14 at 21:15
check [this](http://stackoverflow.com/questions/16993714/mysql-like-and-boolean-mode-fulltext-search) to see what difference in BOOLEAN mode – echo_Me Mar 26 '14 at 21:20
So the BOOLEAN mode is more performant for fulltext columns? Also it'll look for every word seperatly instead of the full string. But it looks like it always returns 1.0? – BlueCacti Mar 29 '14 at 18:19
if you wanna that it will look for every word separatly then consider to make`+` before every word. – echo_Me Mar 29 '14 at 18:55
1

check this answer for how to use http://stackoverflow.com/questions/14572375/mysql-search-for-string-and-number-using-match-against – echo_Me Mar 29 '14 at 18:58

MATCH ... LIKE returns nothing

3 Answers3