How to better check for the existence of data

Help
1

Hello team! I need help to face my issue. I have the table T_CODA_ELABORAZIONE_POD

CREATE TABLE METERING.T_CODA_ELABORAZIONE_POD(
ID_ENTRY_Q  bigint(20) NOT NULL AUTO_INCREMENT,
CODA VARCHAR(100),
POD VARCHAR(20),
ID_OPERATION numeric(10,0),
TYPE_OPERATION varchar(100),
DATAINSERIMENTO datetime,
ISPROCESSED char(1) default  '0',
LOGMESSAGE Varchar(4000),
DATAELABORAZIONE datetime,
DATAINIZIALE date,
	CONSTRAINT T_CODA_ELABORAZIONE_POD_PK PRIMARY KEY (ID_ENTRY_Q)
 )          ;

that contains 16 million rows already on a customer installation. I would like to insert a new record into the table every time except when the POD already exists AND ISPROCESSED = ‘0’. What’s the most performing way to get that?
Thanks.

2

please someone correct me if i’m wrong.

I think the best way is to create a procedure that inserts values into the table if
T_CODA_ELABORAZIONE_POD doesn’t already have a POD with our in_POD input, that has an ISPROCESSED column with ‘0’ for value.

you might have to play around with the procedure a little bit, but this essentially should work:

DELIMITER //
CREATE OR REPLACE PROCEDURE picky_insert (CODA VARCHAR(100), in_POD VARCHAR(20), ID_OPERATION numeric(10,0), TYPE_OPERATION varchar(100), DATAINSERIMENTO datetime, ISPROCESSED char(1), LOGMESSAGE Varchar(4000), DATAELABORAZIONE datetime, DATAINIZIALE date) AS
DECLARE
    q QUERY(count INT) = 
        SELECT COUNT(*) 
        FROM T_CODA_ELABORAZIONE_POD 
        WHERE POD = in_POD 
        AND ISPROCESSED = '0'
    counted_PODS = SCALAR(q)
BEGIN
    IF counted_PODS > 0 THEN
        ECHO SELECT 'no insert!';
    ELSE
        INSERT INTO T_CODA_ELABORAZIONE_POD  VALUES (CODA, in_POD, ID_OPERATION, TYPE_OPERATION, DATAINSERIMENTO, ISPROCESSED, LOGMESSAGE, DATAELABORAZIONE, DATAINIZIALE);
    END IF;
END //
DELIMITER ;

then simply instead of using

INSERT INTO T_CODA_ELABORAZIONE_POD VALUES (insert values...);

now you use

CALL picky_insert(insert values);

and if you want to optimize performance, you ought to make sure that the POD column is indexed with HASH, so the q QUERY(count INT) will find results fast