Beginner: about Sphinx & SphinxQL

Question

I have manage to configure, indexed and run sphinx and now I am using SphinxQL to retrieve some data.

Problem is, when I try to query, the result only gives me the "id". This what confuses me. My data on mySQL composes of the following columns

GENDB_ID //auto increment
GENDB_PDO //product origin, string
GENDB_FPN //family part number, string
GENDB_PN //part number, string

Questions:

1. Why does Sphinx set an alias "id" on my "GENDB_ID"?

2. When I try to specify what columns I want to get on my query I get "column does not exists" error. How can I query certain columns?

3. What is rt? my rt index always get skipped.

This is my config of sphinx:

#
# Minimal Sphinx configuration sample (clean, simple, functional)
#

source src1
{
    type            = mysql

    sql_host        = localhost
    sql_user        = root
    sql_pass        = 1234
    sql_db          = sample
    sql_port        = 3306  # optional, default is 3306

    sql_query       = \
        SELECT * \
        FROM general
}


index test1
{
    source          = src1
    path            = C:/Sphinx/data/test1
    min_infix_len   = 3
}


index testrt //This one doesnt work I don't know why.
{
    type            = rt
    rt_mem_limit        = 128M

    path            = C:/Sphinx/data/testrt
    rt_field    = GENDB_PDO
}


indexer
{
    mem_limit       = 500M
}


searchd
{
    listen          = 9312
    listen          = 9306:mysql41
    log             = C:/Sphinx/log/searchd.log
    query_log       = C:/Sphinx/log/query.log
    read_timeout        = 5
    max_children        = 30
    pid_file        = C:/Sphinx/log/searchd.pid
    seamless_rotate     = 1
    preopen_indexes     = 1
    unlink_old      = 1
    workers         = 2
    binlog_path     = C:/Sphinx/data
    max_matches     = 10000000

}

Answer 1

1. Sphinx just always calls the document-id 'id'. It's not a real attribute, the document-id is critical and hence handled separately to any fields or attributes you have on the index.

2. You can only 'retrieve' ATTRIBUTEs. Only attributes are stored in the index and can be used as is. FIELDs are tokenized and indexed, and so match the full-text query. But the raw text is not stored. (the columns retrieved from your sql_query automatically become FIELDS, unless you specifically configure them as ATTRIBUTES - except the first column, which as noted is special)

   http://sphinxsearch.com/docs/current.html#fields

   http://sphinxsearch.com/docs/current.html#attributes

   You have a choice, either add them as ATTRIBUTES (possibly with sql_field_string, so both attributes and fields). Or accept you can't get them from sphinx, and to get the raw data go back to your mysql database.

   (for the second part, querying certain FIELDS, see the '@' syntax: http://sphinxsearch.com/docs/current.html#extended-syntax )

3. http://sphinxsearch.com/docs/current.html#rt-indexes Real Time Indexes. A very different type of index.