• seguici su feed rss
  • seguici su twitter
  • seguici su linkedin
  • seguici su facebook
  • cerca

SEI GIA' REGISTRATO? EFFETTUA ADESSO IL LOGIN.



ricordami per 365 giorni

HAI DIMENTICATO LA PASSWORD? CLICCA QUI

NON SEI ANCORA REGISTRATO ? CLICCA QUI E REGISTRATI !

Come ridurre il numero di query con "Insert on duplicate key update", in MySQL

di :: 06 novembre 2020
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à!

 
pay per script

Hai bisogno di uno script PHP personalizzato, di una particolare configurazione su Linux, di una gestione dei tuoi server Linux, o di una consulenza per il tuo progetto?

 
 
 
x

ATTENZIONE