NYCPHP Meetup

[nycphp-talk] Tuning MySQL Full Text Search

Jon Baer jonbaer at jonbaer.com
Wed Aug 22 14:47:43 EDT 2007


In other words you should stuff all that FULLTEXT inside of an index  
engine and let it deal w/ it since it is 10x better @ it ...

http://en.wikipedia.org/wiki/Lucene

On Aug 22, 2007, at 12:39 PM, Ben Sgro ((ProjectSkyLine)) wrote:

> Hello Jon,
>
> A lot of what you said went over my head. I no zero experiance with  
> Zend, and I dont know what "Lucene" is.
> I'll look into Zend_Search, and the link provided.
>
> Thanks!
>
> 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.
> ----- Original Message -----
> From: Jon Baer
> To: NYPHP Talk
> Sent: Wednesday, August 22, 2007 12:18 PM
> Subject: Re: [nycphp-talk] Tuning MySQL Full Text Search
>
> 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
>
>
>
> _______________________________________________
> 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
> _______________________________________________
> 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/c1704b14/attachment.html>


More information about the talk mailing list