Asked  8 Months ago    Answers:  5   Viewed   33 times

I want to make a search with fulltext in my web. I need the search with a pagination. my database have 50,000+ rows/per table. I have alter my table and make (title,content,date) to be index. the table is always update, there still have a column id which is automatic increase. and the latest date is always at the end of table.

date  varchar(10)
title  text
content  text

but whole query time will cost 1.5+ seconds. I search the many articles via google, some wrote that only limit Index field word length can help the search more quickly. but as a text type, it can not alter a certain length like that( i have tried ALTER TABLE table_1 CHANGEtitletitleTEXT(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, not work)

date  varchar(10)
title  text(500)
content  text(1000)

so, Except Sphinx and third part script. how to optimization fulltext search with only sql? query code here:

(SELECT 
title,content,date 
FROM table_1 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
UNION 
(SELECT 
title,content,date 
FROM table_2 
WHERE MATCH (title,content,date) 
AGAINST ('+$Search' IN BOOLEAN MODE)) 
Order By date DESC

Thanks.

 Answers

57

Based on the question's follow-up comments, you've a btree index on your columns rather than a full text index.

For MATCH (title,content) against search, you would need:

CREATE FULLTEXT INDEX index_name ON tbl_name (title,content);

I'm not sure it'll accept the date field in there (the latter is probably not relevant anyway).

Wednesday, March 31, 2021
 
Laimoncijus
answered 8 Months ago
53
$sql = mysql_query("SELECT * FROM 
         patient_db WHERE 
         MATCH ( Name, id_number ) 
         AGAINST ('+first_word +second_word +third_word' IN BOOLEAN MODE);");

and if you want to do exact search:

$sql = mysql_query("SELECT * 
                  FROM patient_db 
                  WHERE MATCH ( Name, id_number ) 
                  AGAINST ('"exact phrase"' IN BOOLEAN MODE);");
Wednesday, March 31, 2021
 
Xavio
answered 8 Months ago
86

I had a similar issue when dealing with match against (regarding text length) and my answer was to strlen the string first and switch between like and match against for shorter words. Not what I would call graceful, but it was all I could do since I too had no access to the config.

As for the second question, are you sure the default isn't 4? I recall I couldn't search on the term "art" in my case. 3 letters. Had to go with like on everything below 4 chars.

Wednesday, March 31, 2021
 
Powering
answered 8 Months ago
60

Maybe I am mistaken, but if you search this string +divorce+refinance you get a weird result. If you want to search both words, your should search for +divorce +refinance (with a space between).

I tested it and it returns only one row:

Divorce: Paying Off Spouse = Rate/Term Refinance
Saturday, May 29, 2021
 
jerrygarciuh
answered 5 Months ago
55

Make sure that there is a full text index created on each table.

MySQL can't make a fulltext index across multiple tables. Therefore you should use an index on each table, and do a join to retrieve the rows that match your text.

Saturday, May 29, 2021
 
relyt
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share