NYCPHP Meetup

[nycphp-talk] Tuning MySQL Full Text Search

Ben Sgro (ProjectSkyLine) ben at projectskyline.com
Wed Aug 22 12:39:50 EDT 2007


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20070822/13a63246/attachment.html>


More information about the talk mailing list