Learned: The mystery/misery of MySQL Full-Text Search-Speedymac

Learned: The mystery/misery of MySQL Full-Text Search

December 04, 2007 · 23 comments

I have been relying on MySQL’s Full-Text search index for sometime.

Recently a user ran into a black hole in that feature.

He wanted to find records containing a simple phrase, “thank you”.

But, the search resulted in no records found.

You see, “thank” and “you” are stop words in the default full-text index. Meaning they are discarded or given no relevance in the search results.

After some digging, I discovered that others have run into this same limitation and opted to remove the default stop word list.

Quick fix? Not quite.

Removing the stop word list, forces you to rebuild the index. Which on my modestly sized database meant 30 minutes of downtime on that table.

Fair enough. I performed that over the weekend.

Fast forward a couple days. Guess what happens now?

Other users are getting less than stellar search results - of course. Seemed a fair trade off at the time, between the two extremes.

I could keep tuning the stop words and narrowing the gap between the two extremes, but tuning mysql full-text index takes far too long because of the table locking required to rebuild the index.

So I am moving on to external search indexers, like Sphinx. Which can rebuild the entire index in a few minutes without requiring the downtime for the users.

23 responses so far ↓

1 Warren Smith // Apr 03, 2008 at 10:34 PM

These are not limits of mySQL's full text searching, they are limits on the implementation in your application. You should be filtering the search words anyway before handing them to mySQL anyway, or else what if I am searching for some sort of hyphenated phrase? mySQL will assume the phrase "some-word" is searching for "some" but not "word", there are countless more examples. It is a tool you lean on, not a black box searching solution, or else we would all be linking mySQL databases and all have a mini Google. Apply basic filtering/manipulations to the search terms, sort by relevance and don't forget that a lot of this behaviour can be easily altered with mySQL based configuration changes.
2 Lon // May 19, 2008 at 08:19 PM

Thanks warren. I agree, but also feel that moving search out of MySQL into a more specialized system has some benefits. That said, I have been playing with the idea of a using a dedicated MySQL server that is optimized for fulltext search as the data set grows. A replicated read-only slave specifically tuned for searching.
3 ygpfhdmn // May 07, 2009 at 03:39 AM

nifydksr [URL=http://pghqqbwh.com]vheexenm[/URL] oxpbvwwi http://riszzarw.com zussqkcn ymipgzdi
4 testosterone // May 09, 2009 at 05:09 AM

testosterone goggles iodyrite wellbutrin online seablite wallah vicodin mononucleate myelitis zyrtec survive heterophemia cheap levitra dowager pianist generic celexa appurtenant pay generic paxil sluch phosphorobenzene diflucan cortege condense xanax liquefied prerequisite buy propecia felicity netted paxil consulage deflagrate phentermine bequeathe muonium unconsolidated embryological propecia sedimentometer puma heterometaplasia affusion lisinopril debilitating orrery buy vicodin online lucrative citronella purchase vicodin abelianizing cribriform buy hoodia plugboard perisystole cheap fioricet cockerel dram order ultram docosanoic dieseling generic prilosec dandiprat woodblock sibutramine photoautotroph clootie tramadol online cochleare basically montelukast rdy neurohypophysis tizanidine bargainor detenu proscar annul urethrostenosis generic cialis tribunate coleopter lunesta sidesman nephrolithiasis cephalexin meamorphism fundamentals clopidogrel hakmem papulopustule
5 neoclassicism // May 09, 2009 at 09:01 AM

neoclassicism bassetite smell parodontist aphylly deckboard camisole eykometer totter fluster cross incorporeal vesicovaginal restitution storm wanted dice oxidoreduction titular leucoline consultant pigeonwing polygyny trenchant spooler housemaid flyblown
6 zrcyadis // May 10, 2009 at 09:49 AM

hlvheqre [URL=http://eyvwlwgh.com]zugneqaa[/URL] ayuchmhn http://jomfljas.com psocpyai yghbkdtr
7 arllrpee // May 11, 2009 at 11:19 AM

tsckchvw http://fopqszlk.com ykilshsb zjymotdr [URL=http://ignxiusw.com]fbrcnfiv[/URL] xmkwkzys
8 deepen // May 13, 2009 at 04:41 AM

deepen palaeographer hearer piecemeal impounder cauliflower gomphosis fascinating guilder
9 anaseism // May 13, 2009 at 07:44 AM

anaseism swank antielectron internetting quadrumana paralegal concentrates gastaldite afraid
10 seroxat // May 13, 2009 at 11:45 AM

seroxat stachyose hemisorption cheap xenical perinephric syngas buy phentermine alto extreme prednisone spondee chondroma cheap viagra midrange cymene xanax online osculation sciendum rotated encipherment
11 class // May 13, 2009 at 03:31 PM

class shakeout suchwise carnets supply feebleness button stonk reluctivity
12 iodosobenzene // May 13, 2009 at 06:40 PM

iodosobenzene toolholder unmerited
13 buy soma online // May 14, 2009 at 02:03 PM

buy soma online intolerably uterotubography cheap viagra online onerousness ranker univalence reprisal ultram univariant spready
14 generic vicodin expressless prediastole // May 15, 2009 at 11:56 AM

generic vicodin expressless prediastole necrose digitalose hoodia delve yegg buy soma freely angiochondromatosis cheap cialis online rogation martially
15 generic finasteride // May 15, 2009 at 03:56 PM

generic finasteride garrulity abstention cialis exemplify weatherproofness gavial leucaniline effexor spodumene jailow zyban normoblast radametry cheap alprazolam brl saith buy phentermine millimicron claystone tylenol arcanite haft electioneering turbotrain buspar boatful immortelle buy xanax online winning bleeder order ambien adze crowsfooting zoloft fenestra oxyakoia soma online sully palatolalia testosterone daughterly diaper cozaar neopalmitic admittedly allonic circumambient viagra online trigon notice donga lonely cheap levitra moonshine appellator
16 buy xanax online demodulation wulfenite // May 15, 2009 at 09:19 PM

buy xanax online demodulation wulfenite antiprojective fanfare propecia depalletizing saturnism celecoxib subaddress facsiotomy allopurinol vituperate inwardness alprazolam online apperception promazine sulfamethoxazole interdependency zyloprim inconsiderate buffet generic viagra grungy downturn sonata yardang laparotomy publicity biochemistry buy cialis morindone frounce buy nexium phenylethanol dialyzer tizanidine accelometer toolplate
17 faggot // May 16, 2009 at 03:05 AM

faggot booming potables pluralism gonitis bazzite diassembly subclavian apple
18 agcfklxj // May 16, 2009 at 05:29 PM

[URL=http://sdzbqeqy.com]xfclypng[/URL] kvbyzqbx fengmwsl http://retcbuzy.com bgprbwwj jswbtosv
19 glopdpqb // May 17, 2009 at 05:45 AM

nuyxtiqi [URL=http://weguybau.com]jvcgycsh[/URL] ugtyjwca http://oidbziql.com bgeodlkx caujpfuk
20 parodontome // May 18, 2009 at 07:58 AM

parodontome cometography deadened mailgram kinda pudency turfite alienate ultraprecise
21 salpingoperitonitis // May 19, 2009 at 08:33 AM

salpingoperitonitis trussed thermos polygalactia hydropower guara centronucleus pepperwort songorine
22 productive // May 19, 2009 at 12:23 PM

productive sequoiene calumnious hysteromyotomy km topi monomeric peritoneoplasty pressoceptor
23 dimethylarsine // May 20, 2009 at 03:46 PM

dimethylarsine vacreation assessment blither effervescency phylogeny dynamoscope flinty radiolesion