Come ridurre il numero di query con "Insert on duplicate key update", in MySQL
In un sito ad alto traffico, ma questa regola vale per qualunque sito, occorre ottimizzare il numero di query effettuate al fine di velocizzare il sito stesso.
Quando effettuiamo una INSERT capita spesso di voler prima verificare l'esistenza di un record, e, se già presente, effettuare solamente l'UPDATE dello stesso record.
A questo scopo ci viene il soccorso l'istruzione ON DUPLICATE KEY UPDATE.
Questa istruzione viene utilizzata in occasione di una INSERT e, come detto, ha lo scopo di verificare, prima dell'inserimento del record, che non vi sia una duplicazione di una chiave primaria (PRIMARY KEY) o di una chiave unica (UNIQUE KEY), e, qualora tale chiave già esistesse, effettuerà l'UPDATE al posto dell'INSERT.
In questo modo effettuiamo una sola query invece di due.
Ad esempio, abbiamo una tabella contenente la lista degli IP degli utenti che navigano il nostro sito, e per ogni IP il numero di accessi e la data di ultimo accesso
CREATE TABLE `accessi` (
`accessi_ip` varchar(15) NOT NULL,
`accessi_count` int(10) UNSIGNED NOT NULL,
`accessi_lastdate` datetime NOT NULL,
PRIMARY KEY(`accessi_ip`)
) ENGINE=InnoDB;
Quando un utente accede al sito
- se l'IP non fosse ancora presente, facciamo un INSERT e salviamo l'IP ed "1" come numero di visite
- se l'IP fosse già presente, effettuiamo un UPDATE andando ad aggiungere una unità alle visite.
Dovremmo quindi fare due query: una INSERT per verificare la presenza dell'IP
SELECT * FROM `accessi` WHERE accessi_ip LIKE "91.56.12.14"Se il record non esiste lo creo così
INSERT INTO `accessi` (`accessi_ip`, `accessi_count`, `accessi_lastdate`) VALUES ('91.56.12.14', '1', NOW()); In caso contrario lo aggiorno così
UPDATE `accessi` SET accessi_count=accessi_count+1, accessi_lastdate=NOW() WHERE accessi_ip LIKE "91.56.12.14"Tutto questo può esser fatto con una unica query, questa:
INSERT INTO accessi (accessi_ip,accessi_count,accessi_lastdate) VALUES ('91.56.12.14', 1, NOW())
ON DUPLICATE KEY
UPDATE accessi_tot = accessi_tot + 1;oppure in modo ancora più abbreviato
INSERT INTO accessi VALUES ('91.56.12.14', 1, NOW())
ON DUPLICATE KEY
UPDATE accessi_count = accessi_count+ 1, accessi_lastdate=NOW();E' anche possibile effettuare l'UPDATE solo al verificarsi di certe condizioni.
Ad esempio, vogliamo effettuare l'UPDATE solo se l'ultimo accesso è precedente di 10 minuti rispetto alla data attuale, cioè se "accessi_lastdate > NOW() - INTERVAL 10 MINUTE".
Utilizziamo questa query, in cui dobbiamo ripetere la verifica della condizione in ogni campo.
INSERT INTO accessi VALUES ('91.56.12.14', 1, NOW())
ON DUPLICATE KEY UPDATE
accessi_lastdate = IF(accessi_lastdate > NOW() - INTERVAL 10 MINUTE, NOW(), accessi_lastdate),
accessi_count = IF(accessi_lastdate > NOW() - INTERVAL 10 MINUTE, accessi_count + 1, accessi_count);Provate la query e vedrete che il record non verrà ne creato ne aggiornato, perchè l'avete creato pochi istanti fa, ma se attendete almeno 10 minuti, vedrete che il record si aggiornerà!
