NYCPHP Meetup

[nycphp-talk] Tuning MySQL Full Text Search

Jon Baer jonbaer at jonbaer.com
Wed Aug 22 12:18:02 EDT 2007


I would say build an index if its items which will rarely change.   
There is real power in using a Lucene-based index for full text  
search that a DB does not really focus on and would meet your  
requirements better.  Ive recently put a 200k record set thorough  
Zend_Search and the clients were pretty happy w/ how results could be  
tuned and refined.

Also remember the fulltext parser is pluggable, example ...

http://www.mnogosearch.org/doc/msearch-udmstemmer.html

Has anyone written one for Zend_Search?  Id love to figure out how to  
cross the 2 w/o the need to configure my updates ...

- Jon

On Aug 22, 2007, at 10:50 AM, Ben Sgro ((ProjectSkyLine)) wrote:

> Hello All,
>
> I've got search for very small database working fine. However, I'd  
> like to tune this to have
> different weights for words, because I'm not happy with the search  
> results.
>
> Are there any good books or whitepapers on tuning mysql for  
> fulltext search?
>
> Here's the query:
>
>         $dbObject->DatabaseQuery('SELECT id, title, body, links_to,'
>                                 . ' MATCH(title, body)'
>                                 . ' AGAINST (' . $dbObject->Safe 
> ($searchStr)
>                                 . ' IN BOOLEAN MODE)'
>                                 . ' AS score FROM ' .  
> DATABASE_TABLE_CONTENT
>                                 . ' WHERE MATCH (title, body)'
>                                 . ' AGAINST (' . $dbObject->Safe 
> ($searchStr)
>                                 . ' IN BOOLEAN MODE)'
>                                 . ' ORDER BY score DESC',
>                                 constReturnArray, LOG_LEVEL_DEBUG);
>
> And here's the index code:
>        $dbObject->DatabaseQuery('CREATE FULLTEXT INDEX  
> skillings_index ON ' .
>             DATABASE_TABLE_CONTENT . ' (title, body);',  
> constReturnNone, LOG_LEVEL_DEBUG);
>
> - Ben
>
>
> Ben Sgro, Chief Engineer
> ProjectSkyLine - Defining New Horizons
> Our company: www.projectskyline.com
> Our products: www.project-contact.com
>
> This e-mail is confidential information intended only for the use  
> of the individual to whom it is addressed.
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20070822/6b0472ac/attachment.html>


More information about the talk mailing list