MySQL Forums - Full-Text Search

FULLTEXT initialization 200s (no replies)

a a — Sun, 14 Dec 2025 17:56:11 +0000

I found this issue when migrating servers in 2025/11. I migrated mysql to a server with almost the same configuration. The version is 5.7.44. There are some specific SQL sessions that are particularly long. However, this problem does not occur with the same data and the same SQL statement on other servers, and I tried to revert to 5.7.40 and still has this problem.
https://dba.stackexchange.com/questions/87089/full-text-search-results-in-a-large-amount-of-time-spent-in-fulltext-initializa
My question is almost the same. And all tried. There are still problems. The queried table has 1000w pieces of data.

For example, this one.
select id from problem WHERE MATCH (problem_text) AGAINST ('下面关于Android操作系统的描述中，正确的是' IN BOOLEAN MODE) and is_del = 0 limit 5;
FULLTEXT initialization has 200s. But when I switch to NATURAL LANGUAGE MODE, I don't have this problem. But NATURAL LANGUAGE MODE is too poor at matching short texts

Full Text Searching -- is it possible to speed up query with additional where clauses? (no replies)

Joe Aliperti — Wed, 15 Mar 2023 19:54:19 +0000

I'm trying to optimize full text queries when I have a particular subset of rows I want to search. But I've noticed that the query times do not improve. Am I doing something wrong:

e.g,

SELECT * FROM `blog_posts`
WHERE `id` IN (1,2,3)
AND MATCH (post_body) AGAINST ('+mango +smoothie' IN BOOLEAN MODE);

takes the same amount of time as:

SELECT * FROM `blog_posts`
WHERE MATCH (post_body) AGAINST ('+mango +smoothie' IN BOOLEAN MODE);

So there doesn't seem to be any time gained from drastically reducing the search pool to a small subset. Is there any way to accomplish a speedup in this case?

I'm using the InnoDB engine, id is the primary key, and there is a full text index on post_body. Thanks for any insight!

MATCH Keywords with Spaces AGAINST Large Text (11 replies)

David Allen — Wed, 22 Dec 2021 18:30:01 +0000

We have a keyword database, for example:

1 | MySQL
2 | Database Developer
3 | Software Engineer
4 | Director of Development

And we need a fast way to scan a large set of text (say a resume in base text) to match these exact words, with spaces.

Granted, most of the text we're scanning is well over 1,000 characters, it could be something like:

"I am a Senior Software Engineer with Director of Development experience specializing in JavaScript, JSON, and Web Development. I also have Management experience and have worked in Banking, Cloud, and custom CRM development environments. I am also familiar with .NET and Kittens."

We need to get exact matches for the keyword phrases; for example we want "Director of Development" to match, and not "Director" or "Development" by itself.

Right now we are using REGEXP like this:

SELECT
*
FROM
sit_keyword sk
WHERE
@large_resume_text REGEXP CONCAT('\\b',sk.keyword,'\\b');

It works fine, but it's very slow, and has issues with + and -. But it works - matching the FULL TEXT of the keyword and not just pieces of it.

However, the fastest way to do this would be to use MATCH AGAINST. Note the sit_keyword table has a FULLTEXT index.

e.g.

SELECT
*
FROM
sit_keyword sk
WHERE
MATCH(sk.keyword) AGAINST (@large_resume_text);

The problem is the MATCH AGAINST system breaks the words from the MATCH(col1) up into separate words. For example "Software Engineer" will return a match against the text if either word "Software" or "Engineer" is in the AGAINST target. Even if we put quotes around the col1 keywords, it's the same. This is causing our MATCH AGAINST to pull out keywords that are not true matches.

I tried swapping MATCH to use the large resume text (over 1,000 characters) but AGAINST won't allow the definition of iterative values from our keyword database (at least I don't know how to define that in AGAINST. So it seems we're stuck requiring MATCH to use the database keywords and AGAINST to use the TEXT variable.

The thing is if MATCH AGAINST actually used the whole "phrase" from the col1 keyword (e.g. "Software Engineer") I think it would work just fine. But it won't.

How can we force MATCH AGAINST to match the entire MATCH(col1) value against the target text, and not the individual words in col1? Is there another approach to do this?

Unable to do Full Text Search by Character (no replies)

Dhilip Swaminathan — Thu, 29 Jul 2021 11:11:15 +0000

I am trying to do a full text search in a Aurora MySQL database table. But it is not working if I try to do search based on single character. For ex:

if I type "e", it should return

noodle, eagle, simple, beach

Here is the sql query for it.

SELECT * FROM Property
WHERE MATCH (PropertyName,OwnerEntityName)
AGAINST ('e*' In Boolean Mode);

I have read in few search results, i have to configure ngram_token_size = 1

but i dont know where to configure it in Aurora MySql .

Any help on this would be appreciated.

partial word search with FULLTEXT (3 replies)

Sumit Patel — Thu, 29 Jul 2021 16:30:31 +0000

Query : select * from Student where MATCH (address) AGAINST ( '(india*) (*India)' IN BOOLEAN MODE) ;

which returns all address lines that contain the prefix 'india',
such as

India
IndiaAsia

but not

SPIndiaTest
Test2India

How Can achieve This being * wild character in full text?

Thank You
Sumit Patel

import Arabic data (2 replies)

Ramadan Elfaitouri — Wed, 06 May 2020 23:51:58 +0000

I have tried to import Arabic data by Table Data import wizard from a CSV file, but it does not work.
I have used different Encoding options, but it shows ????? fro source column and data.

Could you please help me to sort it out.

Mysql: InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table? (no replies)

Sam Mathews — Mon, 24 Dec 2018 10:55:08 +0000

Mysql: InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table

I have above log with mysql.
table have fulltext index table did not able to response with above log when i drop the index it works fine.

how to resolve it?

how to handle string with parentheses (no replies)

Adam Chen — Thu, 01 Nov 2018 07:06:56 +0000

Hi,
I have ngram to index my product name and description fields.

Product name may have something like "HP ink(No.21) ..."

when user use "(No.21)" to search for products, ngram matches lots of product with "21"

Is there anyway to handle string with parentheses?

fulltext search with "AND" no results (1 reply)

Blaat Schaap — Thu, 18 Jan 2018 18:46:44 +0000

hey guys,

in germany, the word "und" means "and".

translated example:

if i have a table "books" and a colun "title", i have a book with title
"hunde und katzen" ( => dogs and kats)

my query is

select * from books where match(title) against ('und'); // no results
select * from books where match(title) against ('hunde'); // working

i tried boolean mode, natural language mode etc. it seems like the word "und"
is not indexed or something, maybe because it means AND and is not indexed?

any ideas?

Fulltextsearch + not working as expected (no replies)

Thomas Kernstock — Thu, 14 Dec 2017 10:43:29 +0000

Hi,

I try to test MySQL Fulltextsearch compared to SOLR. I set up an InnoDB table with an int-key and a longtext field including a fulltextindex.

CREATE TABLE cvtext ( id int(11) NOT NULL AUTO_INCREMENT, cvtext longtext NOT NULL, PRIMARY KEY (id), FULLTEXT KEY cvtext (cvtext) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

By the way - I'm using MySQL 4.6.16 and ft_min_word_len=3.

My question is regarding the boolean search:

1) select * from cvtext where MATCH(cvtext) AGAINST('+SQL') => results in:

2 oracle SQL database developer
1 oracle SQL database entwickler
3 oracle SQL database
5 oracle SQL datenbank developer entwickler

2) select * from cvtext where MATCH(cvtext) AGAINST('+SQL +oracle') => results in:

2 oracle SQL database developer
1 oracle SQL database entwickler
3 oracle SQL database
5 oracle SQL datenbank developer entwickler
4 oracle datawarehouse developer entwickler

So the first query is i.m.h.o. correct and returns only records with SQL included. The second query should do the same since I use '+' but it also returns record no 4. which doesn't contain SQL.

Any ideas what could be wrong or did I not understand the query logic?

Full-Text search with join to keyword table (no replies)

Jim Heffernan — Fri, 29 Sep 2017 13:36:51 +0000

I have a large inventory file with a fulltext index on the description field. I also have a keyword table with numerous description keywords. I am looking for a fast way to return any records in the inventory file where the description matches against any keyword in the keyword file and we have stock?

The query below does what I am looking for but it is very slow.. is there a way to use a fulltext match against in the join that would speed up the query?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SELECT
p.partno, p.ohqty, p.descriptio , k.key2
FROM
parts p
INNER JOIN keywords2 k ON p.descriptio LIKE CONCAT('%',k.key2,'%')
where ohqty > 0
order by key2

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

metadata for parser name (no replies)

Serhiy Myrhorod — Wed, 27 Sep 2017 10:08:01 +0000

Where parser name that fulltext index used can be found in metadata?

why did FULLTEXT initialization took so long? (no replies)

lisheng1 zhang1 — Tue, 25 Jul 2017 00:22:11 +0000

Search based on the words (no replies)

Amit Kumar — Tue, 13 Jun 2017 12:49:54 +0000

Dear friends,

Is there any way to consider two words as one.

example if I am searching for
1. ' "green apple" are sweet '
2. ' " green apple " are better then "red apple" '

1 search string : "green apple" are sweet
words present: (i) green apple
(ii) are
(iii) sweet .
2 search string : " green apple " are better then "red apple"
words present :(i) green apple
(ii) are
(iii) better
(iV) then
(v) red apple

in first scenario , it consider "green apple" are only one word while searching. that makes it has three words green apple , and are and sweet.
in second it consider "green apple" as one word , are is second word , better is third word ,then is fourth word and "red apple" is fifth word.

So whenever it does the search , these words will always comes together.

Do i have to write multiple match against clauses here ...

search based on words priority (no replies)

Amit Kumar — Tue, 06 Jun 2017 11:09:59 +0000

Hi Team,

I am working on a search query.
in this query , if I am searching for " word_1 word_2 word_3"
then I want the output in the below priority:

" word_1 word_2 word_3 "
" word_1 word_2 "
" word_1 word_3 "
" word_2 word_3 "
" word_1 "
" word_2 "
" word_3 "

I tried all the option available but not sure how to get this done in a query.

SELECT issue_id,
identifier,
summary,
SUBSTR(content_text,1,200) AS content ,
MATCH(CONTENT_text) against(REPLACE('word_1 word_2 word_3 word_4 ', ' ' , ' +') ) AS score
FROM HOOTSEARCH.HOOT_DATA
WHERE MATCH(CONTENT_text) against(REPLACE('word_1 word_2 word_3 word_4 ', ' ' , ' +') IN BOOLEAN MODE ) ;

here we are using score so we can order the result on the based of priority.

PLEASE HELP

Encrypting Full Text tables? (no replies)

Xing Liu — Wed, 24 May 2017 05:58:54 +0000

So I have a table supporting a chat application we're evaluating (mattermost, to be precise). It uses a 'Posts' folder to store the body text of messages/posts. The same body text must however be searchable.

Since at-rest message encryption is not supported at the application level, I'm using innodb tablespace encryption to satisfy the security requirement, which also protects backups. Binary logs are being purged.

The problem thats left are the full text index auxiliary tables, which are stored in files alongside the user tables because TDE requires file_per_table setting. Is there a way to apply tablespace encryption to these index tables? If not, Is there any other way to protect data in these files? I'm talking about the FTS___INDEX_0 ... tables.

Using sphinxSE in mySQL 5.7 for fulltext search (no replies)

lisheng1 zhang1 — Wed, 10 May 2017 20:52:20 +0000

We are considering to put sphinxSE into mySQL 5.7 as a storage engine for fulltext search (5.7 innoDB fulltext search works for us feature-wise, we mainly want to explore performance improvement).

I am familiar with mySQL build/deployment basic procedures, here i would like to get guidance at high-level (any big pit-fall so it is not doable or no advantage at all, for example), we expect to join innoDB/myISAM tables with sphinx table, any performance pit-falls?

thanks very much for helps!

make full-text search case sensitive (no replies)

Grum De Henseler — Sat, 29 Apr 2017 07:06:52 +0000

Does anyone else get empty set when issuing the following?

select * From articles where match(title, body) against('database') collate latin1_bin;

I created the articles table and populated as per the example in https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

Thanks in advance
Grum

Mysql 5.7 Fulltext search sometimes very slow (no replies)

lisheng1 zhang1 — Tue, 21 Mar 2017 20:07:41 +0000

we have been using mySQL fulltext search for sometime and recently upgraded from 5.5 to 5.7 (using innoDB fulltext search now), it works well feature wise, thanks a lot!

we noticed each morning the 1st fulltext search is usually rather slow (50s), if we repeat same SQL (even with SQL_NO_CACHE) it would be much faster (1-2s), we really need to overcome the initial slowness, your guidance would be really appreciated.

Main Table (which has about 1.2M records):
CREATE TABLE `OBJECT_SEARCH` (
`ID` bigint(20) unsigned NOT NULL,
`DATA` longtext,
`TITLE_DATA` longtext,
`APPEN_DATA` longtext,
PRIMARY KEY (`ID`),
FULLTEXT KEY `SEARCH_IDX` (`DATA`),
FULLTEXT KEY `SEARCH_TITLE_IDX` (`TITLE_DATA`),
FULLTEXT KEY `SEARCH_APPEN_IDX` (`APPEN_DATA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

FT variables (for our app we need to index all common words):
innodb_ft_aux_table | |
innodb_ft_cache_size | 8000000 |
innodb_ft_enable_diag_print | OFF |
innodb_ft_enable_stopword | OFF |
innodb_ft_max_token_size | 84 |
innodb_ft_min_token_size | 1 |
innodb_ft_num_word_optimize | 2000 |
innodb_ft_result_cache_limit | 2000000000 |
innodb_ft_server_stopword_table | |
innodb_ft_sort_pll_degree | 2 |
innodb_ft_total_cache_size | 640000000 |
innodb_ft_user_stopword_table | |

SQL (we need to join a few tables to limit the search scope, without those constraints SQL would be much slower):
SELECT DISTINCT b.ID,b.ACTIVITY_TIME FROM OBJECT_SEARCH AS c straight_join CV_13760_OBJECT_DATA AS b on ((b.ID) = (c.ID)) inner join CV_13760_OBJECTS AS a on ((b.ID) = (a.ID)) WHERE (((((((((((((((((((((((((b.TYPE) = '27') OR (((b.TYPE) = '29') AND ((b.SUBTYPE) IN ('20')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) NOT IN ('23')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR ((b.TYPE) = '2')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('5')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('0')))) OR ((b.TYPE) = '38')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('3')))) OR ((b.TYPE) = '22')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR ((b.TYPE) = '4')) OR ((b.TYPE) = '4')) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('0')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) AND ((match(c.TITLE_DATA) against(' ( +vmoso)' in Boolean Mode) OR match(c.DATA) against(' ( +vmoso)' in Boolean Mode)) OR match(c.APPEN_DATA) against(' ( +vmoso)' in Boolean Mode)) AND ((b.STATUS) IN ('1')) ORDER BY (b.ACTIVITY_TIME) DESC , (b.ID) DESC LIMIT 0,11;

Query plan:
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | a | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 39391 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | vmoso_db.a.ID | 1 | 29.76 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY,OBJECT_TYPE_INDEX,OBJECT_LIFECYCLE_INDEX | PRIMARY | 8 | vmoso_db.a.ID | 1 | 9.05 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+

In query plan i did not fulltext index is mentioned (without using fulltext index how could above SQL get executed?)

Thanks very much for helps

Slow full text index search in Mysql 5.7 (no replies)

Suraj Sawant — Tue, 24 Jan 2017 08:05:28 +0000

Hi All,

We have recently upgraded our Mysql Dev servers from 5.6.27 to 5.7.15.
After upgrade we are facing some issues with full text index.

Query in our case is:

SELECT * FROM (SELECT * FROM TABLENAME WHERE COLUMN2=KEYWORD2)FOO WHERE MATCH(COLUMN1) AGAINST(KEYWORD IN BOOLEAN MODE)

P.N We are filtering rows by using subquery as table has more than 45 million rows.

Before upgrade query used to run within seconds(1-2 seconds max).But after upgrade query is taking minutes.

After checking the plan,we found that query is being optmized into following format as there are some optmization with derived tables in 5.7.15.

SELECT * FROM TABLENAME WHERE COLUMN2=KEYWORD2 AND MATCH(COLUMN1) AGAINST(KEYWORD IN BOOLEAN MODE).

So we disabled the derived table marge by changing the optimizer_switch variables with derived_merge=off.

but then it started giving error-Can't find FULLTEXT index matching the column list

On further investigation,we found that in mysql 5.7.6 onwards internal temp tables engine is changed from myisam to innodb.
so we changed internal_tmp_disk_storage_engine to myisam(full text index run in boolean mode without index creation)

then it started giving error-can not create full text index on materialized subquery.

We tried disabling materialization from optmizer switch variable.but id didn't help.
Need help to resolve this.

Thanks
Suraj

MariaDB and MATCH AGAINST Query (3 replies)

SGK Stmk — Thu, 11 Aug 2016 12:34:23 +0000

Hi

something weird:

- two identical databases with the same dataset. One on my local computer XAMPP & mySQL, the other one on a Diskstation MariaDB.
- an nearly identical table b_entry with a FULLTEXT-index (3 columns: title, sht, lot); engines are InnoDB (mySQL) and myISAM (MariaDB), but this should not take effect.
- an identical SELECT (working on a fulltext-search - yes, it's a bit rough, but it's work in progress :) ):

SELECT DISTINCT b.id, b.title, b.shtext, b.lotext
FROM b_entry b, b_category c, b_entry2user eu
WHERE (
(MATCH(b.title, b.shtext, b.lotext) AGAINST('[searchterm]'))
OR (b.title LIKE '%[searchterm]%' OR b.shtext LIKE '%[searchterm]%' OR b.lotext LIKE '%[searchterm]%')
)
AND b.archive = 0
AND (
(b.categoryId = 4 AND b.id = eu.entryId AND eu.userId = [id])
OR (b.categoryId = c.id AND c.parentId = 3 AND b.id = eu.entryId AND eu.userId = [id])
OR (b.categoryId = c.id AND c.parentId = 2)
OR (b.categoryId = c.id AND c.parentId = 1)
);

Testing with mySQL I do get a resulstset with XX rows.
Testing with MariaDB I get an empty resultset, using the same searchterm and the same id?

What am I missing here?!?

Any help very appreciated!!

FTS remains existent after dropping index (6 replies)

Wolfgang Zuser — Mon, 03 Oct 2016 06:31:07 +0000

We dropped all full text indexes from a Innodb-table. The FTS files are still present in the file System. Even more, at at sartup the system hangs a long time with following transaction:

fetching indexed FTS document

This locks the table for a long time.

How do we get rid of the files and of this Transaction at startup time?

MySQL Full Text Search - So many Indexes (1 reply)

Natasha na — Sun, 20 Mar 2016 05:06:12 +0000

1) I am using XAMPP. MySQL Server version: 10.1.9-MariaDB

Table corp_news has around 10,000 rows.

I am using below query to create Index using PHPMyADmin.

ALTER TABLE corp_news ADD FULLTEXT INDEX `corp_nz` (`content` ASC, `subject` ASC);

After running this query, On left hand side menu, when i click the "index" from the tree i see many indexes named like

content2
content3
content4
.
.
.
.
content37
subject2
subject3
subject4
.
.
.
.
subject36
subject37

I thought it would create only one Index table named "corp_nz". what the heck so many index tables about? Is this normal?

2) If this is the case, how can i even drop the full text index on "corp_news" table?

Distance and Wildcards (3 replies)

Thomas H. — Sun, 13 Mar 2016 05:03:29 +0000

Dear all,

I'm running a full text index on a rather huge database which I'd like to run the following query against:

[...]
MATCH(doc_text) AGAINST ('"+capital* +market* +union*" @5' IN BOOLEAN MODE)
[...]

This query returns results for anything, that contains the words "capital market union" in a max. distance of 5 words.

However, it does not return anything in the distance of 5 words that is "capital markets union" - notice the s in markets, which I'd like to cover with the wildcard. Why?

Of course, if I change it to

[...]
MATCH(doc_text) AGAINST ('+capital* +market* +union*' IN BOOLEAN MODE)
[...]

I get anything that contains capital, market, markets, union, unions separately somewhere in the text.

How can I use distance + wildcard?

Thanks a lot,
Thomas

Problam in Full Text Search (1 reply)

Jaydeep Patel — Mon, 07 Mar 2016 05:07:43 +0000

-- Insert stopwords (for simplicity, a single stopword is used in this example)

mysql> INSERT INTO my_stopwords(value) VALUES ('want');
Query OK, 1 row affected (0.00 sec)

-- Create the table

mysql> CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
phrase TEXT(500)
) ENGINE=InnoDB;

-- Insert data into the table

mysql> INSERT INTO t1(phrase) VALUES
('I want Iphone 6s about');

-- Set the innodb_ft_server_stopword_table option to the new stopword table

mysql> SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';
Query OK, 0 rows affected (0.00 sec)

-- Create the full-text index (which rebuilds the table if no FTS_DOC_ID column is defined)

mysql> CREATE FULLTEXT INDEX idx ON t1(phrase);

mysql> SELECT * FROM t1 WHERE MATCH (phrase) AGAINST ('Iphone' IN NATURAL LANGUAGE MODE);
+----+------------------------+
| id | phrase |
+----+------------------------+
| 1 | I want Iphone 6s about |
+----+------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE MATCH (phrase) AGAINST ('want' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

Till this It works good.

After this I add 'Iphone' in my_stopwords table.

then I run bellow query, and I get result. 'Iphone' is same as 'want', Bellow query should not return any result but it returns.

SELECT * FROM t1 WHERE MATCH (phrase) AGAINST ('Iphone' IN NATURAL LANGUAGE MODE);

Is there any mistake.
I wanted like this. Any search word from 'my_stopwords' table should not return any result.

Regarding FTS and Like queries (4 replies)

Devrishi Shandilya — Sat, 23 Jan 2016 00:38:37 +0000

Hi,

I new with full text search, I have an issue that i have a table catalog_product_entity_varchar that has billions of records (44500000+) and it has a field named value and most of query written in as to search

where `value` like '%Hitachi Brush Cutter%'

I want to use FTS on field value and get benefit of FTS, How can I do this as results are different of both queries, seems something wrong.

select * from catalog_product_entity_varchar where `value` like '%Hitachi Brush Cutter%'
select * from catalog_product_entity_varchar where match(`value`) against ('Hitachi Brush Cutter')

I have user search in natural and bolean mode, but result are not same.

Pl suggest, i am using mysql 5.6.value field is varchar(255) with charset utf8 and table is innodb.

Insert Arabic in the database (1 reply)

Ahmed Nezar — Thu, 15 Oct 2015 23:18:17 +0000

I want to add Arabic to the database by a PHP page I've set the database's collation to utf8_general_ci ad the tables as same as the database. And I used:
mysqli_query($conn, "SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
And
mysqli_query($conn, "SET NAMES 'utf8'");
mysqli_query($conn, "SET CHARACTER SET utf8");
And
mysqli_query($conn, "SET NAMES cp1256");
mysqli_query($conn, "set characer set cp1256");
And escaped the strings but no one had worked the data is like this
متي اصدر..

Which Performs Better? (1 reply)

Michael Lawson — Sat, 22 Aug 2015 19:07:15 +0000

I have three fields defined as fulltext: name, address, city

I know they likely are not equivellent, but which performs better?

A)
INDEX name FULLTEXT on name
INDEX address FULLTEXT on address
INDEX city FULLTEXT on city
SELECT * FROM TABLE
WHERE MATCH(name) AGAINST ('QUERY' IN BOOLEAN MODE)
OR MATCH(address ) AGAINST ('QUERY' IN BOOLEAN MODE)
OR MATCH(city) AGAINST ('QUERY' IN BOOLEAN MODE)

-OR-

B)
INDEX name_address_city FULLTEXT on name, address, city
SELECT * FROM TABLE
WHERE MATCH(name, address, city) AGAINST ('QUERY' IN BOOLEAN MODE)

Full-Text Search Across Multiple Fields (1 reply)

Michael Lawson — Sat, 22 Aug 2015 19:15:01 +0000

I have three fields defined as fulltext: name, address, city

SELECT * FROM TALBE WHERE MATCH(name, address, city) AGAINST ('QUERY' IN BOOLEAN MODE)

If I had the records:
1) James Smith | 123 Anywhere Ave | Springfield
2) Jane Smith | 456 James Ct | Smithville

If I query on "Springfield +James" I would expect to get back JUST #1, but I am getting 1 and 2. Can somebody please explain why, when I request Springfield AND James, I get #2 which only has James?

InnoDB Full Text Search (no replies)

Edwin Desouza — Wed, 08 Jul 2015 23:50:32 +0000

http://www.slideshare.net/mattalord/getting-started-with-mysql-full-text-search