Subject Performance - Querying newly inserted records for processing - which better?
Author Kenneth Foo
Which is faster to query for newly inserted records for some task processing?

METHOD 1 - Use a composite key with a field denoting 'N' for newly inserted rows, 'P' for processed rows. Once processed, update this indexed field from 'N' to 'P'.
METHOD 2 - Use another table to keep the IDs of newly inserted rows

Here's my case.

CREATE TABLE BCAST_MESSAGE_RESPONSES (

PROCESSED_FLAG CHAR(1) CHARACTER SET ASCII NOT NULL,
MESSAGE_ID BIGINT NOT NULL,
RESPONSE_TYPE VARCHAR(10) CHARACTER SET ASCII NOT NULL,
RID INTEGER NOT NULL,
RESPONSE_DATA BLOB
SUB_TYPE TEXT
SEGMENT SIZE 80
CHARACTER SET ASCII,
RESPONSE_TIME TIMESTAMP DEFAULT 'NOW' NOT NULL
);

CREATE UNIQUE INDEX IDX_BCAST_MSGRESP ON BCAST_MESSAGE_RESPONSES(PROCESSED_FLAG,MESSAGE_ID,RESPONSE_TYPE,RID);

Newly inserted items have PROCESSED_FLAG='N'.
A singleton task runs in the background, looks for records where PROCESSED_FLAG='N',
processes them (to increment some summary count), and updates the value to 'P'.

To quicken the search for unprocessed items, PROCESSED_FLAG is part of an index.
Thus what is being done is ...

loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (PROCESSED_FLAG,MESSAGE_ID,RID,...) VALUES ('N',?,?,...);
}

Singleton task would...
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE PROCESSED_FLAG='N';
loop {
UPDATE BCAST_MESSAGE_RESPONSES SET PROCESSED_FLAG='P' WHERE MESSAGE_ID=? AND RID=?;
}


If I'm not mistaken, updating an index would involve a delete and a reinsertion in the index right? (2 ops)
My question is...would it be faster or slower if instead of having a combind index as above, I create another
table keep track of newly inserted items instead? (See below)

CREATE TABLE NEWITEMS (
MESSAGE_ID BIGINT NOT NULL,
RID INTEGER NOT NULL,
PRIMARY KEY(MESSAGE_ID,RID)
);

Thus , my sequence of operations would be
loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (MESSAGE_ID,RID) VALUES (?,?);
INSERT INTO NEWITEMS(MESSAGE_ID,RID) VALUES (?,?);
}

Singleton task would...
SELECT * FROM NEWITEMS;
loop {
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE MESSAGE_ID=? AND RID=?
//Delete record in NEWITEMS to mark that they have been processed
DELETE FROM NEWITEMS WHERE MESSAGE_ID=? AND RID=?
}


Thanks!

Regards
Kenneth

[Non-text portions of this message have been removed]