Geek Answers Handbook

Need sphinx configuration for non-integer primary key

I want to create sphinx search for the 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 cannot change the productID column in the table above.

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 of them provide configuration for sphinx search.

I am using the following configuration.

 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 = 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 get results if I search for productName but not for author and brand

My goal is to get results if the user searches the productName or author or brand buttons

Please give me a suitable configuration.

thanks..

+4
source share
1 answer

One way is to simply generate an integer key

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

because now you can’t associate this with the real product identifier, you can store the prodctID in the attribute to return it to the query results.

You might run into CRC32,

Quick request to check

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

If it works, you're good to go

if not, you have to use 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,...
+2
source

More articles:


All Articles