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 CHANGE
titletitle
TEXT(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.
MySQL FULLTEXT SEARCH (FTS) Full-text search is a searching technique used to examine all words in the document that might not perfectly match the search criteria. The records contain textual data like product descriptions, blog posts, articles, etc.
Like uses wildcards only, and isn't all that powerful. Full text allows much more complex searching, including And, Or, Not, even similar sounding results (SOUNDEX) and many more items.
The columns in the fulltext index can be placed in any sort of order, but all the columns as specified when creating the fulltext index must be referenced in the match() query.
The basic query format of full-text searches in MySQL should be similar to the following: SELECT * FROM table WHERE MATCH(column) AGAINST(“string” IN NATURAL LANGUAGE MODE); When MATCH() is used together with a WHERE clause, the rows are automatically sorted by the highest relevance first.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With