All geek questions in one place

No results after mysql ft_stopword_file removal

I have a database of films that contains information about the film "Yes, we are open."

When searching the database, I have a problem in which the search “yes, we are open” returns a different name that has the word “we” and “open”, but not “yes”, even if I need all the words in logical mode (i.e., "yes, we open", translates to '+yes +we\'re +open' before sending as a request).

I assumed that it was because yes in the built-in list of stop words. However, when I set ft_stopword_file = "" , reload mysql and then repair table [tablename] quick table I am looking for, I have NO results when searching for "yes, we are open." I have included my.cnf below. This is MySQL version 5.0.22. Any ideas?

 [mysqld] query-cache-type = 1 query-cache-size = 8M max_allowed_packet=500M ft_min_word_len=2 ft_stopword_file = "" [myisamchk] ft_min_word_len=2 set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). # old_passwords=1 skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2

EDIT: Here are some sample queries:

# 1 - with an embedded stop file

 mysql> SHOW VARIABLES LIKE 'ft_%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 2 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | +--------------------------+----------------+ 5 rows in set (0.00 sec) mysql> SELECT title, MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) as title_description_genre_country_score FROM `films` WHERE MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) AND `hidden` <> '1' ORDER BY `title_description_genre_country_score` DESC ; +-----------------+---------------------------------------+ | title | title_description_genre_country_score | +-----------------+---------------------------------------+ | Yes, We?re Open | 1 | | Present/Future | 1 | +-----------------+---------------------------------------+ 2 rows in set (0.00 sec)

.... then edit my.cnf, add ft_stopword_file="" .....

# 2 No stop file

 mysql> SHOW VARIABLES LIKE 'ft_%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 2 | | ft_query_expansion_limit | 20 | | ft_stopword_file | | +--------------------------+----------------+ 5 rows in set (0.00 sec) mysql> REPAIR TABLE `films` QUICK; +-------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+--------+----------+----------+ | db.films | repair | status | OK | +-------------------------+--------+----------+----------+ 1 row in set (0.14 sec) mysql> SELECT title, MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) as title_description_genre_country_score FROM `films` WHERE MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) AND `hidden` <> '1' ORDER BY `title_description_genre_country_score` DESC ; Empty set (0.00 sec)

EDIT # 2: Create a table:

 mysql> SHOW CREATE TABLE db.films\G; *************************** 1. row *************************** Table: films Create Table: CREATE TABLE `films` ( `id` varchar(8) NOT NULL default '', `title` varchar(255) default NULL, `hidden` tinyint(1) default '0', `featured` tinyint(1) default NULL, `type` varchar(255) default NULL, `subtype` varchar(255) default NULL, `summary` text, `description` text, `image_url` varchar(255) default NULL, `trailer_url` varchar(255) default NULL, `slug` varchar(255) default NULL, `category` varchar(255) default NULL, `parent` varchar(255) default NULL, `related` varchar(255) default NULL, `sponsor` varchar(255) default NULL, `genre` varchar(255) default NULL, `country` varchar(255) default NULL, `copresenters` varchar(255) default NULL, `original_title` varchar(255) default NULL, `director` varchar(255) default NULL, `executive_producer` varchar(255) default NULL, `producer` varchar(255) default NULL, `cinematographer` varchar(255) default NULL, `writer` varchar(255) default NULL, `editor` varchar(255) default NULL, `sound` varchar(255) default NULL, `cast` varchar(255) default NULL, `language` varchar(255) default NULL, `trt` varchar(255) default NULL, `year` varchar(255) default NULL, `subtitles` varchar(255) default NULL, `format` varchar(255) default NULL, `color` varchar(255) default NULL, `premiere_status` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `id` (`id`), KEY `type` (`type`), KEY `subtype` (`subtype`), KEY `slug` (`slug`), KEY `category` (`category`), KEY `parent` (`parent`), KEY `hidden` (`hidden`), KEY `featured` (`featured`), KEY `copresenters` (`copresenters`), KEY `original_title` (`original_title`), KEY `director` (`director`), KEY `executive_producer` (`executive_producer`), KEY `producer` (`producer`), KEY `cinematographer` (`cinematographer`), KEY `writer` (`writer`), KEY `editor` (`editor`), KEY `sound` (`sound`), KEY `cast` (`cast`), KEY `language` (`language`), KEY `trt` (`trt`), KEY `year` (`year`), KEY `subtitles` (`subtitles`), KEY `format` (`format`), KEY `color` (`color`), KEY `premiere_status` (`premiere_status`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `summary` (`summary`), FULLTEXT KEY `description` (`description`), FULLTEXT KEY `genre` (`genre`), FULLTEXT KEY `country` (`country`), FULLTEXT KEY `title,description` (`title`,`description`), FULLTEXT KEY `title,description,genre,country` (`title`,`description`,`genre`,`country`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
+4
source share
2 answers

Setting the stopwords.txt variable to an empty string ('') disables all filtering by default . (Remove some words you don't need ... sentence ..)

The FULL TEXT SEARCH pointers that you created must be destroyed and create a new one.

FULLTEXT indices must be rebuilt after changing this variable or the contents of the stop file.

Then use REPAIR TABLE tbl_name QUICK.

+2
source

InnoDB does not support table repair (you can see a note when you execute REPAIR TABLE tbl_name QUICK.)

The only solution I got is to change the engine to MyISAM, although this can reduce the performance of READ or WRITE.

How to disable full-text stop words in MySQL:

In the text file my.ini (MySQL):

 ft_stopword_file = "" or link an empty file "empty_stopwords.txt" ft_min_word_len = 2

// set the minimum length, but remember that shorter words (3.2) significantly increase the query time, especially if fields with a full indexed column are large.

Save the file, restart the server.

The next step should be to restore indexes using this query:

 REPAIR TABLE tbl_name QUICK.

However, this will not work if the table uses the InnoDB storage engine. You will need to change it to MyISAM:

 ALTER TABLE t1 ENGINE = MyISAM;

So again:

 1. Edit my.ini file and save 2. Restart your server (this cannot be done dynamically) 3. Change the table engine (if needed) ALTER TABLE tbl_name ENGINE = MyISAM; 4. Perform repair REPAIR TABLE tbl_name QUICK.

Remember that InnoDB and MyISAM have their differences in speed. One reads faster, others write faster (read more about this on the Internet)

0
source

Source: https://habr.com/ru/post/1396714/

More articles:


All Articles