need sphinx configuration for the non integer primary key

i want to create sphinx search for following table structure:

CREATE TABLE IF NOT EXISTS `books` (
  `productID` varchar(20) NOT NULL,
  `productName` varchar(256) NOT NULL,
  `ISBN` varchar(20) NOT NULL,
  `author` varchar(256) DEFAULT NULL,
  `productPrice` float(10,2) NOT NULL,
  `discount` float(10,2) NOT NULL,
  `brandID` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`productID`),
  KEY `status` (`status`),
  KEY `ISBN` (`ISBN`),
  KEY `author` (`author`),
  KEY `brandID` (`brandID`),
  KEY `books_index` (`productName`)
) ENGINE=innodb DEFAULT CHARSET=latin1;

I can't alter the productID column in above table..

i have dependency tables for author and Brands

CREATE TABLE IF NOT EXISTS `authors` (
      `authorID` ini(11) NOT NULL,
      `author_name` varchar(256) NOT NULL
      PRIMARY KEY (`authorID`)
    ) ENGINE=innodb DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `brands` (
      `brandID` ini(11) NOT NULL,
      `brandName` varchar(256) NOT NULL
      PRIMARY KEY (`brandID`)
    ) ENGINE=innodb DEFAULT CHARSET=latin1;

please some one provide configuration for sphinx search.

i am using following config.

source src1
{
        type                    = mysql


        sql_query               = SELECT CRC32(productID) as productid,productID,productName,ISBN,brandID,author FROM sapna_ecom_products

        sql_attr_uint           = productID
        sql_field_string        = ISBN
        sql_field_string        = productName
        sql_field_string        = brandID
        sql_attr_multi         = 开发者_JAVA百科uint brandID from field; SELECT brandID,brandName FROM sapna_ecom_brands
        sql_attr_multi         = uint author from field; SELECT authorID,author_name FROM sapna_ecom_authors

        sql_query_info          = SELECT productID,productName,ISBN,brandID,author  FROM sapna_ecom_products  WHERE CRC32(productID)=$id
}

I am getting results if i search by productName but not for author and brand

My aim is to get the results if user search any by productName or author or brand

Please somebody provide me suitable configuration..

thanks..

One way would be to just generate a integer key

sql_query = SELECT CRC32(productID) AS id,...

because you can't now relate that back to the real productID, you could store prodctID in a attribute so you get it back in query results.

Its possible you might get collisions with CRC32,

A quick query to check

CREATE TABLE test (id INT UNSIGNED NOT NULL PRIMARY KEY) SELECT CRC32(productID) FROM books;

If that works, you good to go,

if not, will have to use a better hashing. See also

http://greenash.net.au/thoughts/2010/03/generating-unique-integer-ids-from-strings-in-mysql/

Alternativly

sql_query_pre = SET @id := 1;
sql_query = SELECT @id := @id + 1 AS id,...