Sphinx Search Index

Question

I'm having an issue with a Sphinx Search Index. I keep getting empty result sets from it.

source mrc
{
    type     = mysql
    sql_host = localhost
    sql_user = root
    sql_pass = 
    sql_db   = test_db
    sql_port = 3306
    mysql_connect_flags = 32

    sql_query_pre = SET NAMES latin1
    sql_query_pre = SET SESSION query_cache_type=OFF
    sql_query_pre = REPLACE INTO sphinx.indexer (idx,server,version,lastrun_at) SELECT 'mrc','localhost','2.0.4',UNIX_TIMESTAMP(NOW()) FROM DUAL
    sql_query_post = UPDATE sphinx.indexer SET fetched_by = UNIX_TIMESTAMP(NOW()) WHERE idx = 'mrc' AND server = 'localhost' AND version = '2.0.4'
    sql_query_post_index = UPDATE sphinx.indexer SET indexed_by = UNIX_TIMESTAMP(NOW()) WHERE idx = 'mrc' AND server = 'localhost' AND version = '2.0.4'

    sql_attr_uint = mrc_id
    sql_attr_uint = mrc_parent
    sql_attr_float = mrc_latitude   
    sql_attr_float = mrc_longitude  

    sql_query = \
        SELECT mrc_id AS doc_id, \
        mrc_id, \
        mrc_parent, \
        RADIANS(mrc_latitude) as mrc_latitude, \
        RADIANS(mrc_longitude) as mrc_longitude \
        FROM mrc_table \
        WHERE mrc_parent > 0

    sql_query_info = SELECT * FROM mrc_table WHERE mrc_id = $id

}

index mrc
{
    charset_type = sbcs
    docinfo = extern
    mlock = 1
    morphology = none
    source = mrc
    path = /usr/local/var/data/mrc
}

indexer gives me -

indexing index 'member_region_cities'...
collected 78312 docs, 0.0 MB
total 78312 docs, 0 bytes
total 0.656 sec, 0 bytes/sec, 119354.57 docs/sec

If I connected to searchd through mysql and do

SELECT * FROM mrc_table;

It returns empty.

Theres definitely data in the localhost table, you can see it where the indexer says 'collected 78312'

So why isn't Sphinx putting the data into searchd?

Cheers

vfedorkov vfedorkov · Accepted Answer · 2012-07-09T18:41:33

Please double check your question. You provided configuration for index called mrc but indexer output reads member_region_cities but I believe it's just shortened name.

Please make sure you run indexer with --rotate option if searchd is up and running.
You may check number of documents id the index using

./indextool --dumpheader /usr/local/var/data/mrc.sph | grep total-documents
Please make sure you are connecting to the Sphinx server, not to MySQL usually it is running on port 9306

mysql -h 127.0.0.1 -P 9306

Hope this helps

Sphinx Search Index

2 Answers