I know this question was discussed a lot of times. Anyway, I would like to figure out again.
Well, I have table "articles" contains these fields:
There is an index on "title" and "keywords". However, there is no index on MEDIUMTEXT fields.
I need to perform "whole word" search on all these fields. I am now doing this using REGEXP:
SELECT * FROM `articles` WHERE `content_body_1` REGEXP '[[:<:]]"keyword"[[:>:]]'
And so on. It's okay for 100 articles, but it's VERY slow (2-3 seconds) on 1000 articles. REGEXP does not use indexing in MySQL. What if I have 10000 articles? Is there any way for faster search by whole keyword?
How can I get that? Is FULLTEXT much faster? If yes - how can I design my database? And also what should I do with FULLTEXT limit of minimum characters to search?
Thanks.
Why are you using a regexp for a full text search? You could just as easily use the % character and it's probably much faster than doing a regex.
SELECT * FROM articles WHERE content_body_1 LIKE '%keyword%'
This will find any rows where your content_body_1 contains the keyword somewhere in it.
FULLTEXT is much faster than REGEXP, when it applies. As a test, I found a word in 4 rows out of 173,979 rows in 0.06 seconds.
You need to do ALTER TABLE tbl ADD FULLTEXT(content_body_1); to build a FT index for that one column.
You can combine multiple columns into a single FT index -- if you want to search across all of them. If you also want to search individual columns, then add single-column FT indexes.
Study the details; MyISAM has one set of caveats; InnoDB has a different set.