Mysql full text returnig NULL

Question

My mysql code.

CREATE TABLE `videos` (
  `id` int(50) NOT NULL,
  `user_id` int(250) NOT NULL,
  `title` varchar(250) NOT NULL,
  `discription` text NOT NULL,
  `video_path` varchar(250) NOT NULL,
  `tumbnail_path` varchar(250) NOT NULL,
  `paid` int(250) NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `videos` (`id`, `user_id`, `title`, `discription`, `video_path`, `tumbnail_path`, `paid`, `date`) VALUES
(1, 4, 'This is a new video', '<p>This is a new video from eduladder&nbsp;in at this video we are discribing how stuffs works</p>\r\n', 'uploadvid/xIdivzexFZXzr6bng2E9mU3PNvMVq0Iz.mp4', 'uploadthump/1AT1EsgJ--6iVLxEwEFRkWa9ADqqD1BG.jpg', 0, '2018-12-10'),
(2, 4, 'New Video for testig', '<p>This is a new video for testing purpose&nbsp;only</p>\r\n', 'uploadvid/_rsIHMc2giVoWV6aRixCoEUk0gKcDhDI.mp4', 'uploadthump/zA_t-2DMusUDvg9xVPwmRAn5-59He76-.jpg', 0, '2018-12-12'),
(3, 4, 'Some New Videos', '<p>This is a record of some new videos</p>\r\n', 'uploadvid/jPzlU3xSJaZVm7EzZu_JfaXq8kAK_1Vc.mp4', 'uploadthump/M_SZodSk20ba2FsXw3X1WVq7a48S_cj3.jpg', 0, '2018-12-13'),
(4, 4, 'Old video', '<p>This is an old video</p>\r\n', 'uploadvid/yaYiDBru2c7fCcosPmrj94JhZ5waxbu8.mp4', 'uploadthump/FhRXXen99DEa0d-8w5m2FDcvFyxlZgx4.png', 0, '2018-12-13'),
(5, 4, 'Almost new video and edited', '<p>This is about almost new video and editted&nbsp;version</p>\r\n', 'uploadvid/YOVPqiFO5xUnCtFAdYzgiY2wzsCnSQ11.mp4', 'uploadthump/MO1faxOKDNESee0gG5SQZYeantzlrPYM.png', 0, '2018-12-13');
ALTER TABLE `videos` ADD FULLTEXT(`title`,`discription`);

And the query which I am excecuting is here.

SELECT * , 
MATCH (title, discription) AGAINST ('New') AS score
FROM videos
WHERE MATCH (title, discription) AGAINST ('New')
ORDER BY score
DESC LIMIT 20

Here's a mysql fiddle https://www.db-fiddle.com/f/jUs9EABZjuBL956WtnTbqx/3

But it is giving me nothing where am I going wrong how can I fix this issue?

Answer 1

Since you asked for it to work with MySql 5.5 in your comment:

Plz see the udated code db-fiddle.com/f/jUs9EABZjuBL956WtnTbqx/3 innodb wont work it is myql version <5.6

Then it's 2 different case. For MySql 5.7, Stopword list applies only.

But for MySql 5.5 from your latest fiddle here are the 2 reasons :

The 1st reason this is not working is because the word you are searching for is present in 50% or more of the total rows, so it is considered as Common Word and won't match as a result. See the Mysql 5.5 FullText Search Docs :

There are three types of full-text searches:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators, with the exception of double quote (") characters. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.

And the 2nd one is because by default the FullText Search Length is set to 4. So you need to change it in my.cnf and add value :

[mysqld]
ft_min_word_len = 3

to be able to search 3 characters words.

But since in db-fiddle I cannot modified the length, here is a modified working fiddle where the word Older is used. Older is not present on 50% of rows and its length is >= 4.

Answer 2

Seems to be an issue with Full text Stop-words on the myisam engine, I changed the engine to InnoDB and I can get results.

See this link. Full-Text Stopwords

To fully disable stopwords, add this ft_stopword_file = '', to your database configuration file, the repair the table to rebuild indexes, REPAIR TABLE tbl_name QUICK. and restart the server