Sphinx DocumentID for complex queries

development Add comments

Every document in Sphinx has to have an unique Document ID. Usually it’s the database table’s primary key value.

But what if your table doesn’t have a primary key or is a very complex query?

In that case we used this approach.
1. Create a table called _sequence with just one integer field called id.
2. Run this query:

DELIMITER //
DROP  FUNCTION IF EXISTS GET_NEW_ID; //
CREATE FUNCTION GET_NEW_ID( ) RETURNS INT READS SQL DATA
BEGIN
    UPDATE _sequence SET id = LAST_INSERT_ID(id+1);
    SET @id = LAST_INSERT_ID();
    IF 0 = @id THEN
        INSERT INTO _sequence VALUES (0); -- fix if there were no data to increment
    END IF;
    RETURN @id;
END; //
DELIMITER ;

3. In your Sphinx queries you can use this function to generate an unique document ID:

SELECT GET_NEW_ID() as id, title, ...

Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in