Sphinx RT Index and SphinxQL Query

Question

We are deploying the RT index in our architecture. But we need some clarification and some difficulties faced during the deployment.

Schema defined in Index:

        index logtable
        {
        type = rt
        path = /usr/local/sphinx20/var/data/logtable
        rt_attr_string = TransactionId
            rt_attr_uint = CustomerId
        rt_attr_timestamp = DateOfTransaction
        rt_attr_string = CustomerFeedback
        rt_field = TransactionType
        }

Faced Problem

Question 1: How we can get the count() query result in SPHINXQL. Because its important for us, based on customer count we have to take it to show in our application.

Example below,

Query - select count(*) from logtable where CustomerId='871';   

In SphinxQL - We didnt get this result and getting the following error.ERROR 1064 (42000): index logtable: invalid schema: Count(*) or @count is queried, but not available in the schema.

Question 2: i declared as a STRING attribute in conf for the field of "TransactionId", but i cant able to retrieve the records if that fields use in where condition.

    Example below, 

    select * from logtable where TransactionId='TRA23454'; 

Following error i am getting, ERROR 1064 (42000): sphinxql: syntax error, unexpected $undefined, expecting CONST_INT or CONST_FLOAT or '-' near '"TRA23454"'

Please help us to close these issues if knows.

Kumaran

Answer 1

select * from logtable where TransactionId='TRA23454';

Answer :

select * from logtable where  MATCH('@TransactionId TRA23454')

Answer 2

In first example instead of count(*) you need to use 'show meta;' query after search query, it will contain total_count field.

select id from logtable where CustomerId='871';   
show meta;

In the second example string attributes can't be used in WHERE, ORDER or GROUP clauses. Actually you need to convert TransactionId into integer and use integer attribute. It is quite simple to do using crc32 mysql function.