Sphinx search engine, a couple of quick questions.

Question

So, I'm just starting to read up on this, I've never implemented a search in PHP before. I have a couple of questions that I was wondering:

• By the sounds of things, Sphinx needs a 'daemon', a program running in the background, to operate?
• Say I built an index of a mySQL table, then a user uploads another record. For the search to show this record, would I have to build the index over and over, every time a user updates / creates a record?

Thanks.

Answer 1

You can use an incremental indexing scheme, see here.

Answer 2

Basically you need a base index then a delta index. The delta updates anything in the base. So you might run the base index at 1 am then every 5 minutes update the delta. You store a timestamp in either a table or another database and that controls which records go in the delta.

You can then look at sql_query_killlist to remove things that are deleted, see the docs.

An example conf might look like this.

source _user
{
    type = mysql
    sql_host = localhost
    sql_user = root
    sql_pass =
    sql_db = db
    sql_port = 3306
    sql_attr_uint = category_id
    sql_attr_timestamp = time_posted
}

source user_base : _user
{
    sql_query_pre = REPLACE INTO sphinx_lastrun (object_name, object_value) SELECT 'users', @max_stamp:=UNIX_TIMESTAMP(MAX(stamp))-1 FROM users
    sql_query = SELECT id, category_id, name, UNIX_TIMESTAMP(stamp) AS stamp FROM users WHERE stamp < FROM_UNIXTIME(@max_stamp)
    sql_query_info = SELECT id FROM users WHERE id=$id  
}

source user_incr : _user
{
    sql_query = SELECT id, category_id, name, UNIX_TIMESTAMP(stamp) AS stamp FROM users WHERE stamp >= (SELECT FROM_UNIXTIME(object_value) FROM sphinx_lastrun WHERE object_name = 'users')

    # You'll need sql_query_killlist to get rid of stuff that is deleted. See the docs

    sql_query_info = SELECT id FROM users WHERE id=$id   
}

index _user
{
    docinfo = extern
    mlock = 0
    morphology = none
    min_word_len = 2
    charset_type = sbcs
    min_prefix_len = 3
    enable_star = 1
}

index user_base : _user
{
    source = user_base
    path = /opt/sphinx/var/data/user_base
}

index user_incr : _user
{
    source = user_incr
    path = /opt/sphinx/var/data/user_incr
}

index user
{
    type = distributed
    local = user_base
    local = user_incr
}

searchd
{
    listen = 3312
    log = /opt/sphinx/var/log/searchd/searchd.log
    query_log = /opt/sphinx/var/log/searchd/query.log
    pid_file = /opt/sphinx/var/log/searchd/searchd.pid
}

Answer 3

To answer your first question: yes Sphinx comes with a daemon that runs in the background and performs searches when instructed to do so.