Hello, please sign in or register
You are here: Home

FULL-TEXT fine tunning

Fulltext is the cream in my coffee, it can make very fast and accurate text searches on varchar() and text() and i believe tinytext() and char() but havent's checked fields, of a MyISAM table

Setting up process

The configuration of the full text indexing is set in my.ini, the basic database server parameters are

# Use stopwords from this file instead of built-in list.

ft_stopword_file = "D:/mysql/ft_stop_word.txt"

# the default for this is a built in list follow this link to read more 
# http://dev.mysql.com/tech-resources/articles/full-text-revealed.html#stopwords

ft_min_word_len = 1

The my.ini file is typically located in

C:/windows/my.ini

once editing this the mysql service will have to be restarted, this can be achieved under windows by either 1. In command prompt type NET stop mysql 2. Somewhere in

Administrative Tools > Services

you shall see the mysql program named, with options start, stop and resart.

However its not a good idea to do this on a live mysql server, ( suggest replicating the server and changing assigning ports ... more on that another time).

Depending on whether you have already assigned the FULLTEXT field(s) in your table(s) use,

1. REPAIR TABLE table_name

or

2. CREATE FULLTEXT INDEX index_name ON tbl_name (colnames)

 

Stop Words

Stop words is a list used to negate words from the index, when created, and the mysql match query's. It is set by default in

my.ini

ft_stop_words = {built-in}.
Stop word indexing is only used with MyISAM tables and fields with FULLTEXT indexing. When the Index is created words in the list will be absent, from the index, this helps reduce the size of the index to vastly speed up search returns.

Creating Query's

I want to search for "Hitchhiker's guide to the Galaxy" in the column of my table called title. My query will look like

SELECT * FROM book WHERE MATCH(title) AGAINST('+Hitchhiker''s +guide +to +the +galaxy' IN BOOLEAN MODE) 

 

Notice the double apostrophe ''. An apostrophe is used to delimit an apostrophe '. Also IN BOOLEAN MODE is always useful. Allows for better design but their are operators to learn.

+
A leading plus sign indicates that this word must be present in every row returned.
-
A leading minus sign indicates that this word must not be present in any row returned.
*
An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word.
"
A phrase that is enclosed within double quote ('"') characters matches only rows that contain the phrase literally, as it was typed.

Additonal operators set in ft_boolean_syntax = + -><()~*:""&|. These characters will not be stored in the index

Writing queries for Plurals or Singular words

Yep you guessed it! Recreate a new column for searching. hmmm.... Use a server script to remove the plural stuff from the end of a search string so If i were to type "hitchhiker", "hitchhikers" or "hitchhiker's" all results would be the same because the column is actually "hitchhiker" and Your code knows to remove any trailing "s'"'s and "'s"'s. Clear?... Good!

Here's how i created the new column

 // Create column in table  
ALTER TABLE book ADD COLUMN title_search VARCHAR(255) AFTER title    
// Populate the column  
UPDATE book SET title_search = REPLACE( REPLACE( CONCAT( REPLACE(  
// Replace any commas with space              
REPLACE(title,', ', ' ')  
// Replace semi-colons with space 
 , '; ', ' '),  
// Append a space to the end of the string (will become clear)     
SPACE(1)) 
 // Replace apostrophe+"s"+space with space
        , "'s ", " ")  
// Replace "s"+space with space      , "s ", " ")      
// Index the column 

 ALTER TABLE book ADD FULLTEXT ft_title_search (title_search) 

Example

I have a table of 2.2 million books records, and FULLTEXT indexing on title, and author fields. I wanted to find books titled only I can cook and Bravo two Zero. I specified a minimum word length to index of '1'. so the search could include the words "two" and "I" And create my own stop word file. Why because the word "zero" is one of the default stop words, and 10% of the titles include the word "the" which when searching strings like Far from the madding crowd were slow. So included in my stop word file was the preposition "the". My ft_stop_word.txt file now looks like

the, a, and, an, on, if, or, of

A list of the default stop words

Default {built-in} Stop Word List

a's, able, about, above, according, accordingly, across, actually, after, afterwards, again, against, ain't, all, allow, allows, almost, alone, along, already, also, although, always, am, among, amongst, an, and, another, any, anybody, anyhow, anyone, anything, anyway, anyways, anywhere, apart, appear, appreciate, appropriate, are, aren't, around, as, aside, ask, asking, associated, at, available, away, awfully, be, became, because, become, becomes, becoming, been, before, beforehand, behind, being, believe, below, beside, besides, best, better, between, beyond, both, brief, but, by, c'mon, c's, came, can, can't, cannot, cant, cause, causes, certain, certainly, changes, clearly, co, com, come, comes, concerning, consequently, consider, considering, contain, containing, contains, corresponding, could, couldn't, course, currently, definitely, described, despite, did, didn't, different, do, does, doesn't, doing, don't, done, down, downwards, during, each, edu, eg, eight, either, else, elsewhere, enough, entirely, especially, et, etc, even, ever, every, everybody, everyone, everything, everywhere, ex, exactly, example, except, far, few, fifth, first, five, followed, following, follows, for, former, formerly, forth, four, from, further, furthermore, get, gets, getting, given, gives, go, goes, going, gone, got, gotten, greetings, had, hadn't, happens, hardly, has, hasn't, have, haven't, having, he, he's, hello, help, hence, her, here, here's, hereafter, hereby, herein, hereupon, hers, herself, hi, him, himself, his, hither, hopefully, how, howbeit, however, i'd, i'll, i'm, i've, ie, if, ignored, immediate, in, inasmuch, inc, indeed, indicate, indicated, indicates, inner, insofar, instead, into, inward, is, isn't, it, it'd, it'll, it's, its, itself, just, keep, keeps, kept, know, knows, known, last, lately, later, latter, latterly, least, less, lest, let, let's, like, liked, likely, little, look, looking, looks, ltd, mainly, many, may, maybe, me, mean, meanwhile, merely, might, more, moreover, most, mostly, much, must, my, myself, name, namely, nd, near, nearly, necessary, need, needs, neither, never, nevertheless, new, next, nine, no, nobody, non, none, noone, nor, normally, not, nothing, novel, now, nowhere, obviously, of, off, often, oh, ok, okay, old, on, once, one, ones, only, onto, or, other, others, otherwise, ought, our, ours, ourselves, out, outside, over, overall, own, particular, particularly, per, perhaps, placed, please, plus, possible, presumably, probably, provides, que, quite, qv, rather, rd, re, really, reasonably, regarding, regardless, regards, relatively, respectively, right, said, same, saw, say, saying, says, second, secondly, see, seeing, seem, seemed, seeming, seems, seen, self, selves, sensible, sent, serious, seriously, seven, several, shall, she, should, shouldn't, since, six, so, some, somebody, somehow, someone, something, sometime, sometimes, somewhat, somewhere, soon, sorry, specified, specify, specifying, still, sub, such, sup, sure, t's, take, taken, tell, tends, th, than, thank, thanks, thanx, that, that's, thats, the, their, theirs, them, themselves, then, thence, there, there's, thereafter, thereby, therefore, therein, theres, thereupon, these, they, they'd, they'll, they're, they've, think, third, this, thorough, thoroughly, those, though, three, through, throughout, thru, thus, to, together, too, took, toward, towards, tried, tries, truly, try, trying, twice, two, un, under, unfortunately, unless, unlikely, until, unto, up, upon, us, use, used, useful, uses, using, usually, value, various, very, via, viz, vs, want, wants, was, wasn't, way, we, we'd, we'll, we're, we've, welcome, well, went, were, weren't, what, what's, whatever, when, whence, whenever, where, where's, whereafter, whereas, whereby, wherein, whereupon, wherever, whether, which, while, whither, who, who's, whoever, whole, whom, whose, why, will, willing, wish, with, within, without, won't, wonder, would, would, wouldn't, yes, yet, you, you'd, you'll, you're, you've, your, yours, yourself, yourselves, zero

Conclusion

Yep use it, Dont load a lot of Data into a table with a fulltext index (it will make a sloath of your server)

Read these

 

Comments

Full Text Search Query
i want to search book name "play with me", and i am using the same match against like as [MATCH(bookname)AGAINST('+"play" +"with" +"me"' IN BOOLEAN MODE)]
Created 03/09/14
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share